DISTINCT Clause in MariaDB

MariaDB uses SQL (Structured Query Language) and is an open-source relational database management system (RDBMS) for managing and manipulating data. With the help of the MariaDB DISTINCT clause, you can efficiently extract unique values from a given column or collection of columns in query results. In SELECT queries, this clause is frequently used to remove duplicate rows and show only unique data. Let’s examine the specifics of MariaDB’s DISTINCT clause.

DISTINCT Clause

To remove duplicate records from the result set, the SELECT query is used with the MariaDB DISTINCT clause.

Syntax:

SELECT DISTINCT col1, col2,….. FROM table_name;

SELECT keyword: list the columns from which you wish to extract unique values.
DISTINCT keyword: comes right after the SELECT keyword and the database engine is instructed to deliver only unique values for the designated columns.

Create Table

Query:

CREATE TABLE worker (
    worker_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2),
Hire_date DATE
);

Insert Data

Query:

INSERT INTO worker (first_name, last_name, department, salary, hire_date)
VALUES
    ('Minal', 'Pandey', 'IT', 60000.00, '2022-01-15'),
    ('Kavya', 'Sharma', 'HR', 55000.00, '2022-02-20'),
    ('Kavya', 'Sharma', 'Finance', 70000.00, '2022-03-10'),
    ('Asad', 'Mohammad', 'Marketing', 62000.00, '2022-04-05'),
    ('Vivek', 'Sharma', 'IT', 65000.00, '2022-05-12');

Example 1: To Extract Unique Departments

Query:

SELECT DISTINCT department FROM worker;

This query will retrieve a list of unique department from worker table.

Output:

DISTINCT Clause

Example 2: Count the Number of Unique Values in first_name Column

Query:

SELECT COUNT(DISTINCT first_name) FROM worker;

This query will count the unique first_name in the worker table.

Output:

DISTINCT Clause

Example 3: Remove Duplicates From the Table

Query:

SELECT DISTINCT first_name FROM worker WHERE first_name='Kavya';

This query will distinct first_name from the worker table where the condition is first_name should be Kavya.

Output:

DISTINCT Clause

Example 4: Find Unique Hire Dates

Query:

SELECT DISTINCT DATE(hire_date) AS unique_hire_date FROM worker;

This query will retrieve a list of unique hire dates from the worker table aliasing the result as unique_hire_date.

Output:

DISTINCT Clause

Example 5: List of Employees Get Hired in Particular Year

Query:

SELECT DISTINCT first_name, last_name, hire_date FROM worker WHERE YEAR(hire_date) = 2022;

This query will give the list of employees who were hired in the year 2022.

Output:

DISTINCT Clause

Conclusion

A useful tool in MariaDB for extracting unique values from a dataset is the DISTINCT clause. Writing more accurate and efficient queries can be achieved by using DISTINCT, regardless of the size of the database you are dealing with. To optimize your query results, consider the unique requirements of your application and use the DISTINCT clause tactfully.

Article Tags :

Databases

MariaDB