DISTINCT Clause in MariaDB
Last Updated :
01 Dec, 2023
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.
Share your thoughts in the comments
Please Login to comment...