How to Use Distinct in SQL to Retrieve Values Without Duplicates

During your IT interview, you may be asked to use Distinct in SQL to retrieve values without having duplicates. It is one of the most common tasks related to SQL. Let us tell you the main things you need to mention to make a good impression on your interviewers. Giving a complete answer has never been simpler.

What Is Distinct in SQL?

Distinct in SQL is a commonly used keyword meant to retrieve values from a table and eliminate duplicates. The SQL SELECT DISTINCT statement will only retrieve unique values from the selected table.

How to Use Distinct in SQL Correctly

To get a better idea of what you need to do to use Distinct in SQL properly, we are going to take an example. This will help you visualize exactly what you need to do.

Distinct in SQL Example

For out Distinct in SQL example, we created a table called “Celebrities” where we included the birthplace of various artists.

Celebrities

Celebrity_Name Celebrity_Birthplace
Michael Jackson United States
Arnold Schwarzenegger Austria
David Bowie United Kingdom
Madonna United States
Celine Dion Canada

 

Our Aim: Retrieving the unique values from the “Celebrity_Birthplace” table without having any duplicates. (by using Distinct in SQL)

How to Do It

We used the Distinct in SQL keyword to retrieve the unique values from the “Celebrity_Birthplace” table and it automatically eliminated any duplicates (in our table, the values “United States” was repeated). This is what this simple code looked like:


SELECT DISTINCT Celebrity_Birthplace from Celebrities;


This is what our Distinct in SQL code returned:

Celebrity_Birthplace
United States
Austria
United Kingdom
Canada

The Distinct code only returned the value “United States” once because it is meant to retrieve unique values only.

Another aspect that you should know is that the Distinct in SQL keyword can be used on multiple tables at the same time. Let’s suppose our table included a celebrity twice and it looked like this.

Celebrity_Name Celebrity_Birthplace
Michael Jackson United States
Arnold Schwarzenegger Austria
David Bowie United Kingdom
Madonna United States
Celine Dion Canada
Madonna United States

If we wanted to use the SQL Distinct keyword on both tables, we would have to write the following code:


SELECT DISTINCT Celebrity_Name, Celebrity_Birthplace from Celebrities;


This code would eliminate the duplicates from both tables and it would return the following values:

Celebrity_Name Celebrity_Birthplace
Michael Jackson United States
Arnold Schwarzenegger Austria
David Bowie United Kingdom
Madonna United States
Celine Dion Canada

When used for both tables, the SELECT DISTINCT statement eliminates identical combinations of values (in our example, Madonna and United States). However, it will include the value “United States” twice in the Celebrity_Birthplace table because the statement is aimed to retrieve unique combinations, rather than unique values.

We hope this was useful to you and that you can use this on your interview. Good luck from the IT interview Guide team!

Image Source: Pixabay

Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.