How to use DISTINCT in SQL

How to use DISTINCT in SQL

3 mins read1.4K Views Comment
Vikram
Vikram Singh
Assistant Manager - Content
Updated on Mar 31, 2023 16:38 IST

DISTINCT in SQL is used to remove the duplicate values in the result i.e. it return only unique values of a column. In this article, we will briefly discuss how to use DISTINCT statement in SQL.

2022_08_SQL-DISTINCT.jpg

While working on a large dataset, you will get multiple repeated entries, i.e., duplicate values, and when you run a query, it will increase time complexity. So how to deal with these duplicate values in SQL?
In SQL, we have a DISTINCT statement that returns only unique values.
In this article, we will briefly discuss SQL DISTINCT statement.

Table of Content

What is SQL DISTINCT statement?

SQL DISTINCT statement is used to remove the duplicate values from the dataset, i.e., DISTINCT returns only unique values (rows).

  • Used with SELECT statement in conjunction
  • Can be used with aggregate functions: COUNT, AVG, MIN, MAX, etc.
  • DISTINCT in SQL operates only on a single column
  • SQL DISTINCT doesn’t ignore NULL values
    • i.e., the result will include NULL as a distinct value 

Must Read: How to CREATE, UPDATE, INSERT and DELETE SQL views?

Must Read: All about DML Command in SQL

Syntax

 
SELECT DISTINCT column1, column2,....
FROM table_name;
Copy code

Note

  • When there is only one column, DISTINCT will return unique values of that column
  • When there is more than one column, DISTINCT will return a unique combination for the selected column

Now, let’s take some examples of distinct queries in SQL.

Let us have an employee dataset that has EmployeeID, FirstName, LastName, Gender, Education, Country, Department, and Salary of Employees.

Employee ID FirstName LastName Gender Education Country Department CTC(in Lacs)
1001 Ajay Singh M Doctoral USA Tech 25
1002 Babloo Gupta M PG India Management 10
1003 Chhavi Tiwari F UG China Sales NULL
1004 Dheeraj Singh M UG Australia Marketing 12
1005 Evina Johnson F PG India Management NULL
1006 Fredy Carl M UG USA Tech 23
1007 Garima Singh F Intermediate Finland Admin 8
1008 Hans Sewak M UG China Sales 10
1009 Ivanka Trump F Intermediate Finland Admin NULL
1010 Jai Tiwari M High School Finland Admin NULL

DISTINCT query in SQL

Single Column

Example 1: Determine the unique Last Name from the above employee dataset.

Query

 
SELECT DISTINCT LastName
FROM Employee;
Copy code

Output

Last Name
Singh
Gupta
Tiwari
Johnson
Carl
Sewak
Trump

Must Read: Introduction to SQL

Must Read: SQL ACID Properties

Multiple Column

Example 2: From the employee table, find the unique country, and department combination.

Query

 
SELECT DISTINCT Department, Country
FROM Employee;
Copy code

Output

Department  Country
Tech USA
Management India
Sales China
Marketing Australia
Admin Finland

Must Read: Joins in SQL

Must Read: SQL INNER JOIN

As we mentioned above, SQL DISTINCT doesn’t ignore NULL values, so let’s have an example to check it:

Example 3: Find the unique record of salary from the employee dataset.

Query

 
SELECT DISTINCT CTC (in Lacs) AS CTC
FROM Employee;
Copy code
CTC
25
10
NULL
12
23
8

Must Read: SQL LIMITS

Must Check: SQL Online Course and Certifications

DISTINCT with WHERE

Example 4: From the employee table find the education detail of all the employee who are working in the admin department. 

Query

 
SELECT DISTINCT Education
FROM Employee
WHERE Department = ‘Education’;
Copy code

Output

Education
Intermediate
High School

Must Read: What is the difference between SQL and MySQL?

Must Read: Difference between SQL and NoSQL

Now, if we want to arrange the CTC (unique record) of the employees in descending order. Then for that, we can use ORDER BY with DISTINCT.

Let’s have an example:

DISTINCT with ORDER BY

Example 5: Arrange the CTC (unique record) of the employees of employee dataset in descending order.

Query

 
SELECT DISTINCT CTC (in Lacs) AS CTC
FROM Employee
ORDER BY CTC DESC;
Copy code

Output

CTC
25
23
12
10
8
NULL

NOTE:

  • MySQL considers NULL values lower than Non-NULL values

Must Read: What is the difference between SQL and MySQL?

Must Read: Difference between SQL and NoSQL

COUNT with DISTINCT

COUNT is an aggregation function that returns the number of different values in a column, but when it combines with DISTINCT, it returns only unique (non-null) values.

Example 6: Count the number of the distinct (non-null) CTC of employees in employee dataset.

Query

 
SELECT COUNT (DISTINCT (in Lacs)) AS CTC
FROM Employee;
Copy code

Output

5

Conclusion

In this article, we have briefly discussed how to use DISTINCT statement in SQL with the help of examples.

Hope this article, will help you in your data science/data analysis journey.

Keep Learning!!

Keep Sharing!!

Must Read: SQL WHERE

Must Read: Introduction to Normalization

About the Author
author-image
Vikram Singh
Assistant Manager - Content

Vikram has a Postgraduate degree in Applied Mathematics, with a keen interest in Data Science and Machine Learning. He has experience of 2+ years in content creation in Mathematics, Statistics, Data Science, and Mac... Read Full Bio