What is DISTINCT in SQL?

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.

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.

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.