MySQL DISTINCT Clause

MySQL is a relational database management system that can store data and we can query the stored data using SQL. SQL is a standard language to manipulate the database. The data fetching process can be applied with many filters as we might need only some specific data, or we might want to exclude some data then we can apply a filter using something called – ‘CLAUSE‘ in SQL. In this article, we’ll be discussing the ‘DISTINCT‘ clause.

MySQL DISTINCT Clause

The DISTINCT clause filters and removes duplicate records from the resultset; hence, unique records will be left. For example, if one cell value is repeated multiple times in that same column, that value will be shown only once in the resultset. Please note that the DISTINCT clause only removes the duplicate values from the resultset and NOT the database.

MySQL DISTINCT Clause

The above figure demonstrates the working of MySQL SELECT DISTINCT, as it excluding all the duplicate records and fetching only single instances of each type.

Syntax:

SELECT DISTINCT column_names FROM Table_Name;

Let’s look at the following example

Assuming that we have a database called – ‘geeksforgeeks‘ and a table called – ‘employees‘ in it. Fetching the data without using the DISTINCT keyword fetches us all the records that are present in the ‘employees‘ table. (Our table contains redundant values)

Without DISTICT Clause

But when we apply the distinct keyword using the following query:

SELECT DISTINCT * FROM employees;

Output

with DISTINCT clause

MySQL and NULL Values

In general, using DISTINCT comes very handy when the result set or the target table doesn’t contain the NULL values. But, assuming the target table contains the NULL values. See what happens

DISTINCT with NULL

If we don’t want NULL values in our result set, then we have to be more specific in our query and let SQL know that we don’t want NULL values in our result set and this can be done using the following clause and a comparison operator:

WHERE Clause

WHERE Clause is another most commonly used filter that allows us to be more specific and fetch the records based on some condition.

• Fetches the records that fulfill the specified condition
• It can be used with SELECT, UPDATE, and DELETE.

Syntax

SELECT column_names FROM Table_name WHERE specify_condition;

IS NOT NULL Operator

IS NOT NULL is a comparison operator that ensures that our result set will not contain the NULL values.

In our case, we need to specify the condition where the column’s value is not NULL. So we’ll use this comparison operator.

Query

SELECT DISTINCT email FROM employees WHERE email IS NOT NULL; 

Output:

Output

Note: If we’re using DISTINCT and specifying the column names explicitly then DISTINCT is applied to all the columns specified after the DISTINCT clause. Example:

 SELECT DISTINCT name, email FROM employees; 

DISTINCT is applied to both columns ‘name’ and ’email’.

DISTINCT Keyword in Case of Multiple Columns

Although DISTINCT fetches the unique records from a table it may not always amount to be useful in certain scenarios when we have to find unique records from multiple columns. For example, consider the below scenario:-

We have an ‘orders‘ table which has the following columns: order_id, customer_id, product_id, order_date.

Query

CREATE TABLE orders (
         order_id INT PRIMARY KEY,
         customer_id INT,
         product_id INT,
         order_date DATE
);

INSERT INTO orders VALUES
    (1, 101, 1, '2023-01-10'),
    (2, 102, 2, '2023-01-15'),
    (3, 101, 1, '2023-01-20'),
    (4, 103, 2, '2023-02-01'),
    (5, 102, 3, '2023-02-05');

Consider the Below Problem

You’re required to fetch the order date of every product in the table along with the product’s ID. (Two columns)

See what happens when we apply the DISTINCT keyword here

Output

Explanation

• DISTINCT clause is applied on two columns here – product_id and order_date
• Both columns when combined has unique value but individually, they have the same values which causes issue.

The DISTINCT clause doesn’t work here because we’re trying to fetch unique records from multiple columns. DISTINCT considers all the records to be unique because –

product id in 1st row has order_date = 2023-01-10 and product_id in 3rd row has order_date 2023-01-20. Both are unique when we combine both the columns but are individually different and this is not what we wanted to achiever. So, to resolve this issue we must make use of – the GROUP BY clause.

GROUP BY

It is used to group the records based on a column or set of columns and then perform operations. See the below query:-

SELECT product_id, order_date
FROM orders
GROUP BY product_id;

Output:

Output

Explanation: Unique products are fetched along with their order_date.

Conclusion

In this article, we’ve discussed the overall working of a DISTINCT clause in MySQL. It helps us in fetching all the unique records without modifying the table and getting a non-redundant result set. DISTINCT is a result filtering clause that fetches only unique records from the table. When the table contains NULL values we must specify further filtering using WHERE clause and IS NOT NULL comparison operator. DISTINCT doesn’t work properly when multiple columns are grouped together to fetch unique records hence we must use GROUP BY clause.