The DISTINCT clause is a powerful feature in MySQL that allows you to retrieve unique rows from a result set obtained through a SELECT statement. It eliminates duplicate values and ensures that only distinct values are returned. This article aims to provide a detailed explanation of the DISTINCT clause in SELECT statements, along with examples to illustrate its usage.
Syntax: The syntax for using the DISTINCT clause in a SELECT statement is as follows:
SELECT DISTINCT column1, column2, ...
FROM table_name
WHERE conditions;
The DISTINCT keyword is placed immediately after the SELECT keyword, followed by a comma-separated list of columns that you want to retrieve unique values for. It can be used with one or more columns.
Example 1: Retrieving Distinct Values from a Single Column Let's consider a simple table named "employees" with a column called "department" containing duplicate department names:
+----+------------+
| ID | Department |
+----+------------+
| 1 | HR |
| 2 | Finance |
| 3 | HR |
| 4 | Marketing |
| 5 | Finance |
+----+------------+
To retrieve only the distinct department names, you can use the following query:
SELECT DISTINCT Department
FROM employees;
Output:
+------------+
| Department |
+------------+
| HR |
| Finance |
| Marketing |
+------------+
Example 2: Retrieving Distinct Values from Multiple Columns
Suppose we have another table named "orders" that contains information about customer orders, including the customer name and their respective cities:
+----+--------------+--------------+
| ID | CustomerName | City |
+----+--------------+--------------+
| 1 | John | London |
| 2 | Mary | Paris |
| 3 | John | London |
| 4 | Peter | New York |
| 5 | Mary | Paris |
+----+--------------+--------------+
To retrieve distinct combinations of customer names and cities, you can use the following query:
SELECT DISTINCT CustomerName, City
FROM orders;
Output:
+--------------+--------------+
| CustomerName | City |
+--------------+--------------+
| John | London |
| Mary | Paris |
| Peter | New York |
+--------------+--------------+
Example 3: Using DISTINCT with Other Clauses The DISTINCT clause can also be combined with other clauses such as WHERE, ORDER BY, and GROUP BY to further refine your result set.
For instance, if we want to retrieve distinct department names from the "employees" table, where the department name starts with "M," we can use the following query:
SELECT DISTINCT Department
FROM employees
WHERE Department LIKE 'M%';
Output:
+------------+
| Department |
+------------+
| Marketing |
+------------+
The DISTINCT clause in the SELECT statement is a useful tool in MySQL to obtain unique values from a result set. It helps in reducing redundancy and provides a streamlined approach to retrieve specific information. By combining the DISTINCT clause with other clauses, you can tailor your queries to meet specific requirements. Understanding how to utilize the DISTINCT clause will enhance your ability to query and analyze data effectively in MySQL.
Example 4: We have two tables: "employees" and "departments." The "employees" table contains information about individual employees, including their names, salaries, and department IDs. The "departments" table contains details about different departments, such as department IDs and department names. We will use the JOIN operation to combine these two tables and then demonstrate how the SELECT DISTINCT clause works in this context.
Create the tables and insert sample data:
CREATE TABLE departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(50)
);
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(50),
salary DECIMAL(10, 2),
department_id INT,
FOREIGN KEY (department_id) REFERENCES departments(department_id)
);
INSERT INTO departments (department_id, department_name)
VALUES
(1, 'HR'),
(2, 'Finance'),
(3, 'Marketing');
INSERT INTO employees (employee_id, employee_name, salary, department_id)
VALUES
(1, 'John Doe', 50000, 1),
(2, 'Jane Smith', 60000, 2),
(3, 'Mike Johnson', 55000, 1),
(4, 'Emily Brown', 70000, 3),
(5, 'David Lee', 62000, 2);
Retrieve distinct department names along with their employee count:
SELECT DISTINCT d.department_name, COUNT(e.employee_id) AS employee_count
FROM departments d
JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name;
Output:
+----------------+----------------+
| department_name | employee_count |
+----------------+----------------+
| HR | 2 |
| Finance | 2 |
| Marketing | 1 |
+----------------+----------------+
In this example, we used the SELECT DISTINCT clause along with a JOIN operation to fetch distinct department names from the "departments" table. We also calculated the count of employees in each department using the COUNT() function. The JOIN operation links the "employees" table with the "departments" table based on the common "department_id" column.
As a result, the query returns a list of department names along with the count of employees in each department. Since we used the SELECT DISTINCT clause, each department name is displayed only once, even if multiple employees belong to the same department.
Note: When using SELECT DISTINCT with joined tables, it's essential to include the necessary columns from both tables in the SELECT and GROUP BY clauses to ensure accurate results. Additionally, you can apply various other filtering conditions using the WHERE clause to further customize the output.