Back to cookbooks list Articles Cookbook

How to not Show Duplicates in SQL

  • DISTINCT

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.

idnamecountry
1MadridSpain
2BarcelonaSpain
3WarsawPoland
4CracowPoland

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:

country
Spain
Poland

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.

Recommended courses:

Recommended articles:

See also: