SQL SELECT DISTINCT Statement

SELECT DISTINCT Statement is used to fetch unique records from a table. It only returns distinct values in the result.

Lets say we have a table “Employee” having a field “EmployeeDepartment”. Since each department can have several employees thus this field of table would be having duplicate values. Suppose we would like to fetch the department names alone from this table. In that case it would be wise to use DISTINCT Keyword since we do not want to have bunch of duplicate values.

There are several cases where DISTINCT keyword could be very useful. Lets see the syntax and example of SQL SELECT DISTINCT statement to understand it better.

SELECT DISTINCT Syntax

SELECT DISTINCT column_name1, column_name2,... 
FROM TableName;

SELECT DISTINCT Example

We have a table STUDENT with the following records:

+---------+----------+-----+-----------+----------+
| ROLL_NO | NAME     | AGE | BRANCH    | CITY     |
+---------+----------+-----+-----------+----------+
|  10001  | Kate     |  22 | CSE       |  Mumbai  |
|  10002  | Richard  |  21 | ECE       |  Delhi   |
|  10003  | Rick     |  33 | ME        |  Chennai |
|  10004  | Peter    |  26 | CSE       |  Delhi   |
|  10005  | Steve    |  22 | CSE       |  Noida   |
|  10006  | Mark     |  22 | ECE       |  Jaipur  |
|  10007  | Brad     |  23 | ME        |  Rampur  |
+---------+----------+-----+-----------+----------+

Fetch the unique “Branches” from the STUDENT table –

SELECT DISTINCT BRANCH FROM STUDENT;

Result:

+----------+
| BRANCH   |
+----------+
|  CSE     |
|  ECE     |
|  ME      |
+----------+

Lets say you want to sort the result. You can do so by using Order by clause (We have discussed ORDER BY clause in separate tutorial) along with Distinct –

SELECT DISTINCT BRANCH FROM STUDENT
     ORDER BY BRANCH DESC;

Result:

+----------+
| BRANCH   |
+----------+
|  ME      |
|  ECE     |
|  CSE     |
+----------+

SELECT DISTINCT multiple columns

In the above example, we have retrieved the data of only one column from a table. Lets see what happens when we use the SELECT DISTINCT statement with multiple columns –

Consider this ORDER table:

CUSTOMER_NAME  BILL_AMOUNT   ORD_NUM
----------     -----------   -------
Rick             2000         1901
Rick             2000         1902
Rick             3000         1903
Rick             3000         1904
Rick             4500         1905
Steve            2000         1906

SQL Statement:

SELECT DISTINCT CUSTOMER_NAME,BILL_AMOUNT
FROM ORDER;

Output:
When we select multiple columns using SELECT DISTINCT statement, then the data of those columns combined is treated as a distinct value. As you can see in this example that Rick 2000 rows and Rick 3000 rows were present multiple times in the ORDER table so when we DISTINCT select these rows based on these columns, we got these rows only once in the output.

CUSTOMER_NAME  BILL_AMOUNT
----------     -----------
Rick             2000
Rick             3000
Rick             4500
Steve            2000