SQLite - DISTINCT Keyword

DISTINCT is a optional keyword in SQLite indicating that the answer should not contain duplicates..

In SQLite, the default is that duplicates are not eliminated! (Result is called a “multiset”).

There can be situations where multiple duplicates entries are present in table records. DISTINCT keyword helps us to fetch the unique values from the database

Syntax:

If DISTINCT specified then duplicate rows from the result set will be eliminated.

SELECT DISTINCT column_name1, column_name2
FROM table_name;

Example:

So let's consider we have created SCHOOL database. And we have created a STUDENTS table in this database.

The STUDENTS table have five columns (ID, NAME, SURNAME, AGE, ADDRESS).

The STUDENTS table also have some data inside it as shown below:

ID          NAME        SURNAME     AGE         ADDRESS
----------  ----------  ----------  ----------  ----------
1           Mark        Osaka       20          Munich
2           Tom         white       21          Cologne 
3           John        Rossman     19          Essen
4           John        Khan        22          Bonn
5           Julia       Tesar       18          Berlin
6           Tim         Netten      20          Frankfurt
7           John        Mevric      17          Wuppertal
                            

First we will see the result of normal select query.

sqlite> SELECT name FROM STUDENTS;

The result of above query is shown below:

NAME       
---------- 
Mark       
Tom          
John       
John       
Julia    
Tim        
John        
                            

Now we will use DISTINCT keyword with the select query.

sqlite> SELECT DISTINCT name FROM STUDENTS;

The result of above query is shown below:

NAME       
---------- 
Mark       
Tom          
John       
Julia    
Tim