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