SQL DISTINCT keyword

SQL DISTINCT keyword is used to retrieve unique ( distinct ) values from the table. If there are duplicate values present for a particular column, then SQL “distinct” keyword can be used to select only the distinct values.

SQL DISTINCT Syntax
SELECT DISTINCT column1, column2, columnN ..... FROM table_name;

where column1, column2, columnN are the column names of the table.

In this post, we are going to look at the below things one by one

Sample Table

Employee

IDNAMESALARYADDRESSDESIGNATIONAGE
1Peter Clark90000.00Daphne Road ManukauManager36
2Alfredo Smith60000.00Pimpama Drive RotoruaPrincipal Engineer32
3James Cook40500.00Saint-Antoine QuebecSDE I27
4Hudson Stewart45000.00Stoney Creek OntarioSDE II30
5Nathan Taylor55000.00Long Street, WoolstonSDE II31
6Michal Clarke53000.00Baker Street, LondonPrincipal Engineer30
7William Johnson60000.00Charlton Road, SturbridgePrincipal Engineer35


Using DISTINCT on only one column

The following statement will display all of the DESIGNATION column values from the employee table.

SELECT DESIGNATION FROM Employee;

DESIGNATION
Manager
Principal Engineer
SDE I
SDE II
SDE II
Principal Engineer
Principal Engineer


WITH DISTINCT

The following statement will display only the unique DESIGNATION from the employee table.

SELECT DISTINCT DESIGNATION FROM Employee;

DESIGNATION
Manager
Principal Engineer
SDE I
SDE II

Using DISTINCT on multiple columns


WITHOUT DISTINCT
select SALARY, DESIGNATION from employee;

SALARYDESIGNATION
90000.00Manager
60000.00Principal Engineer
40500.00SDE I
45000.00SDE II
55000.00SDE II
53000.00Principal Engineer
60000.00Principal Engineer

WITH DISTINCT

Now, we will try to use the DISTINCT keyword on two of the columns ( SALARY and DESIGNATION ).

select DISTINCT SALARY, DESIGNATION from employee;

SALARYDESIGNATION
90000.00Manager
60000.00Principal Engineer
40500.00SDE I
45000.00SDE II
55000.00SDE II
53000.00Principal Engineer

Here, we can see that the number of rows has been decreased by 1 after using the DISTINCT keyword. This is because the uniqueness will now be defined by the two columns combined ( Salary and Designation ) and that is the reason the row containing Principal Engineer designation and 60000.00 salary is printed only once, even though it was present two times in the actual table.

Hope you have liked the article. If you have any doubts or concerns, please feel free to write us in the comments or mail us at [email protected]

Liked the article? Share this on

Leave a Comment

Your email address will not be published.