What is the SQL SELECT DISTINCT statement?
The SQL SELECT DISTINCT statement returns values that differ from each other. It ignores the duplicate values from the column where the DISTINCT statement is applied and only returns different values.
Syntax
SELECT DISTINCT column1, column2, ...FROM table;
Students
StudentID | StudentName | City | Country |
1 | Saad Qureshi | Michigan | U.S.A |
2 | John Snow | Berlin | Germany |
3 | Erin White | NewYork | U.S.A |
4 | Ethan Goldberg | Paris | France |
Example without the Distinct statement
This example shows the effect of using a normal SELECT statement instead of SELECT DISTINCT. This will not filter out the redundant values of the column.
CREATE DATABASE test;CREATE TABLE Students (StudentID int,StudentName varchar(20),City varchar(20),Country varchar(20));INSERT INTO StudentsVALUES (1, 'Saad Qureshi','Michigan','U.S.A');INSERT INTO StudentsVALUES (2, 'John Snow','Berlin','Germany');INSERT INTO StudentsVALUES (3, 'Erin White','NewYork','U.S.A');INSERT INTO StudentsVALUES (4, 'Ethan Goldberg','Paris','France');SELECT Country FROM Students;
Output
This statement outputs all the values of the country column including the redundant values. The repetitive value is ‘U.S.A’ in this case as it is shown twice. This is the implementation without DISTINCT.
Example using the Distinct statement
Now the effect of using a SELECT DISTINCT statement is shown by the following code. This will filter out the redundant values of the column.
CREATE DATABASE test;CREATE TABLE Students (StudentID int,StudentName varchar(20),City varchar(20),Country varchar(20));INSERT INTO StudentsVALUES (1, 'Saad Qureshi','Michigan','U.S.A');INSERT INTO StudentsVALUES (2, 'John Snow','Berlin','Germany');INSERT INTO StudentsVALUES (3, 'Erin White','NewYork','U.S.A');INSERT INTO StudentsVALUES (4, 'Ethan Goldberg','Paris','France');SELECT DISTINCT Country FROM Students;
Output
This statement outputs only those values of the country column that are distinct(not redundant). This country column only has 3 distinct values which are displayed on the output. Although the U.S.A exists twice in the country column, the DISTINCT statement filters out the redundant value and returns the U.S.A only once.
