Suppose we have the Employee table below, and we want to retrieve all of the cities that the employees live in, but we don’t want any duplicates. How can we do this in SQL?

 
employee
employee_name employee_location
Joe New York
Sunil India
Alex Russia
Albert Canada
Jack New York
Alex Russia

In SQL, the distinct keyword will allow us to do that. Here’s what the simple SQL would look like:

SELECT DISTINCT employee_location from employee;

Running this query will return the following results:

employee_location
New York
India
Russia
Canada

So, you can see that the duplicate values for "Russia" and "Canada" are not returned in the results.

It’s worth noting that the DISTINCT keyword can be used with more than one column. That means that only the unique combination of columns will be returned. Again, this is best illustrated by an example.

Suppose we run the following SQL:

SELECT DISTINCT employee_name, employee_location from employee;

If we run the SQL above, it will return this:

employee_name employee_location
Joe New York
Sunil India
Alex Russia
Albert Canada
Jack New York

Note that the one extra entry for "Alex, Russia" is missing in the result set above. This is because when we select a distinct combination of name and location, if there are 2 entries with the same exact name and location then the sql that we ran above will only return one of those entries.

Hiring? Job Hunting? Post a JOB or your RESUME on our JOB BOARD >>

Subscribe to our newsletter for more free interview questions.