The Distinct keyword in SQL is used to prevent duplication of data in SQL. Distinct keyword is mostly used check whether a particular record is present in a table. In a SQL code, the use of DISTINCT keyword is allowed only once. However number of columns specified in a SELECT DISTINCT statement is not restricted.
The Basic Syntax for SQL is given below,
SELECT DISTINCT column1, column2,…..columnN
FROM table_name
WHERE [condition]
When to use SELECT DISTINCT?
Databases is all about adding and retrieving data but in some cases you may want to retrieve a data without duplicated results. SELECT DISTINCT statement is used with conditions and columns to retrieve a particular data present in a table. SELECT DISTINCT statement is the only way to retrieve a unique data in SQL. However Oracle uses both DISTINCT and UNIQUE keywords to retrieve a unique data but DISTINCT keyword is more effective than UNIQUE.
Extension of standard SQL using SELECT DISTINCT
Let us consider an example of sports academy which offers training in various fields. During the enrollment of a new batch, the sports academy may want to know which is most popular game among the students. If a table named as enrollment consist of student details, the popular sport can be displayed using following code.
Table Name: enrollment
Columns: STUDENT_ID, STUDENT_CLASS
SELECT * FROM enrollment
The table will be displayed as
| 001 | Swimming |
| 002 | Football |
| 003 | Football |
| 004 | Tennis |
| 005 | Cricket |
| 006 | Football |
| 007 | Tennis |
| 008 | Badminton |
| 009 | Cricket |
| 010 | Squash |
- Recommended: Final year Academic projects download
The number of sport streams selected by students during new enrollment can be found by SELECT DISTINCT keyword. The following SQL code will be used to find the number of sport stream selected by students without repeated values.
SELECT DISTINCT STUDENT_CLASS
FROM enrollment
If required, Where condition can be used along with the above code. The output will be
STUDENT_CLASS
Football
Tennis
Cricket
Badminton
Squash
SELECT DISTINCT can be used to retrieve value from columns which can complete an array of tasks. SELECT DISTINCT also increases the readability of the output. SELECT DISTINCT canbe used to retrieve values from specific columns.
Consider that we have a table which contains runs scored by the batsmen. The table which contains the batsmen names, runs scored and date.
TOP_ODI_SCORE
| Batsmen | Runs | Date |
| Sachin | 200 | 4/5/2010 |
| Rohit | 238 | 6/8/2014 |
| Sehwag | 206 | 3/2/2012 |
| Gayle | 203 | 2/9/2013 |
| De Villers | 204 | 8/8/2015 |
- Learn: Aptitude Q&A
To display the name of the batsmen who score top ODI runs can be displayed using the following code.
SELECT DISTINCT Batsmen
FROM TOP_ODI_SCORE
The output for the above code will be
Batsmen
Sachin
Rohit
Sehwag
Gayle
De Villiers
The top ODI scores can be displayed using the following code
SELECT DISTINCT Runs
FROM TOP_ODI_SCORE
The output for the above code will be
Runs
200
238
206
203
204
The date in which the top ODI scores scored can be displayed using
SELECT DISTINCT Date
FROM TOP_ODI_SCORE
The output for the above code will be
Date
4/5/2010
6/8/2014
3/2/2012
2/9/2013
8/82015