How to use DISTINCT in SQL
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.
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
Best-suited Data Analytics courses for you
Learn Data Analytics with these high-rated online courses
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;
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 LastNameFROM Employee;
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, CountryFROM Employee;
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 CTCFROM Employee;
| 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 EducationFROM EmployeeWHERE Department = ‘Education’;
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 CTCFROM EmployeeORDER BY CTC DESC;
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 CTCFROM Employee;
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
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
Top Picks & New Arrivals
