How to not Show Duplicates in SQL

Problem:

You’d like to display non-duplicate records in SQL.

Example:

Our database has a table named City with data in the columns id, name, and country.

Let’s get the names of the countries without duplicates.

Solution:

We’ll use the keyword DISTINCT. Here’s the query:

SELECT DISTINCT country
FROM City;

Here’s the result of the query:

Discussion:

If you want the query to return only unique rows, use the keyword DISTINCT after SELECT. DISTINCT can be used to fetch unique rows from one or more columns. You need to list the columns after the DISTINCT keyword.

How does it work under the hood? When the query is executed, the whole set of data is selected first, then DISTINCT removes the rows that are duplicated given the selected columns.

In our example, both Spain and Poland occur twice in the table. However, after applying the keyword DISTINCT, each of them is returned only once.