Skip to content
Tutorials Center
My learning

Topics

Lesson 16 of 125 ยท Querying Data

DISTINCT

Remove duplicate rows with SQL SELECT DISTINCT. Learn DISTINCT on several columns, how NULLs are treated, COUNT(DISTINCT), DISTINCT vs GROUP BY and DISTINCT ON.

Ask the shop database "which countries do our customers live in?" with a plain SELECT country FROM customers and you get 40 rows, one per customer, with Colombia repeated six times. What you wanted was the list of different values. That's what DISTINCT does: it removes duplicate rows from a result.

DISTINCT is easy to use but easy to misuse, too. In this lesson you'll learn exactly what counts as a duplicate, how DISTINCT works with several columns and with NULL, how to count distinct values, and why reaching for DISTINCT to "fix" a query with duplicate rows is often a sign of a deeper problem.

SELECT DISTINCT

Put DISTINCT right after SELECT. Compare the two queries:

SQL Duplicates and DISTINCT ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The first query lists one row per customer (only the first ten are shown here), so Australia appears four times. The second returns each country exactly once: 13 rows, from Australia to USA.

DISTINCT doesn't change the table; it only removes duplicates from the result of this one query. The opposite keyword, SELECT ALL, keeps duplicates. It's the default, so you'll rarely see it written out.

DISTINCT on several columns

DISTINCT applies to the whole row of the result, not to the first column. With two columns, a row is a duplicate only if both values match another row:

SQL Distinct combinations ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

You get one row per combination of country and city: 13 rows here, the same as the number of countries, because each country in this shop has exactly one city. Try it on the company database, where the combinations are more interesting:

SQL Distinct job titles per department ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Engineering (department 1) has three distinct job titles and Support (department 4) has three as well, even though those two departments have 16 employees between them.

DISTINCT is not a function. People sometimes write SELECT DISTINCT(title), first_name FROM employees hoping to get one row per title. The parentheses do nothing: they just wrap the expression title. The query is SELECT DISTINCT title, first_name, which removes only rows where both columns repeat. To get one row per title with "some" name from each group, you need GROUP BY or a window function (see the end of this lesson).

DISTINCT and NULL

Although NULL = NULL is unknown (see Working with NULL), DISTINCT treats all NULLs as duplicates of each other and keeps one. Some students in the school database have no homeroom yet:

SQL NULL is kept once ยท database: school

Press Run (or Ctrl + Enter) to see the result here.

The result has four rows: NULL, A, B and C. The NULL row stands for "students without a homeroom", and it appears only once even though several students have none. The standard calls this being not distinct: for duplicate removal, two NULLs are considered the same.

Counting distinct values

count(DISTINCT column) counts how many different non-NULL values a column has. It answers questions like "how many countries do we sell to?" or "how many different products have ever been ordered?":

SQL COUNT(DISTINCT) ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The shop has 40 customers in 13 countries, and they share only 25 different first names (there are several Mayas, Rosas and Taras). The second query shows 292 order lines but only 120 different orders, because one order can contain several products, and all 26 products have been ordered at least once.

Remember the NULL rule for aggregates: count(DISTINCT homeroom) in the school database is 3, not 4, because aggregate functions skip NULLs, while SELECT DISTINCT homeroom returns the NULL as a row.

Other aggregates accept DISTINCT too: sum(DISTINCT x), avg(DISTINCT x) and group_concat(DISTINCT x) work on the set of different values. Use them carefully: sum(DISTINCT price) adds each distinct price once, so two different products with the same price count only once, which is rarely what you mean. You'll go deeper in Aggregate Functions.

SQL
-- PostgreSQL and SQL Server don't allow count(DISTINCT a, b); use a subquery
SELECT count(*)
FROM (SELECT DISTINCT country, city FROM customers) AS pairs;

DISTINCT and ORDER BY

With SELECT DISTINCT, the sort columns usually have to be in the SELECT list. Think about why: if you select distinct countries but sort by signed_up, which customer's sign-up date should a country use? There are several.

SQL
-- Error in PostgreSQL, SQL Server and MySQL: ORDER BY column must appear in the select list
SELECT DISTINCT country
FROM customers
ORDER BY signed_up;

SQLite is more permissive and runs this query, but the order it produces is not meaningful: it sorts by the sign-up date of whichever row it happened to keep for each country. If you want "countries ordered by their first customer", say exactly that with grouping:

SQL Order distinct values by a summary ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

France and Turkey are the oldest markets, both with customers who signed up in February 2023.

DISTINCT vs GROUP BY

These two queries return the same rows:

SQL
SELECT DISTINCT country FROM customers;

SELECT country FROM customers GROUP BY country;

Most databases even execute them the same way. The difference is what you can do next. DISTINCT only removes duplicates; GROUP BY creates groups you can summarize, such as count(*) per country. Use DISTINCT when you want the list of values, and GROUP BY as soon as you need numbers about each value. GROUP BY explains grouping in detail.

SQL DISTINCT versus GROUP BY ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The first query lists the four statuses; the second also tells you how many orders have each one.

DISTINCT is not a fix for duplicate rows

When a query joins tables and returns the same row several times, it's tempting to add DISTINCT and move on. Sometimes that's correct, for example when you really want "customers who placed at least one order" and each customer has many orders. But often the duplicates point to a join that multiplies rows, and DISTINCT hides the symptom while the numbers you compute stay wrong (a total summed before DISTINCT is still inflated). Before adding DISTINCT, ask why the duplicates are there. You'll learn the patterns in Joining Many Tables, and the cleaner alternative, EXISTS, in EXISTS.

DISTINCT also has a cost: the database must sort or hash the whole result to find duplicates. On a few thousand rows that's nothing; on millions it's real work, so don't add it "just in case".

One row per group: DISTINCT ON

A common need is "one whole row per group", such as the cheapest product in each category. Plain DISTINCT can't do this, because it compares all selected columns. PostgreSQL has a special form, DISTINCT ON, which keeps the first row of each group according to ORDER BY:

SQL
-- PostgreSQL only
SELECT DISTINCT ON (category_id) category_id, name, price
FROM products
ORDER BY category_id, price;

Other databases, including SQLite, use a window function for the same job: number the rows in each group and keep number 1.

SQL Cheapest product per category ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

You get six rows, one per category, each with its cheapest product, starting with the Silicone Spatula Set in category 1. Don't worry if row_number() OVER (...) looks new: ROW_NUMBER, RANK and DENSE_RANK explains it step by step.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. A table has the rows (Lyon, France), (Paris, France) and (Lyon, France). How many rows does SELECT DISTINCT city, country return?

    DISTINCT compares whole rows. (Lyon, France) appears twice and is kept once, and (Paris, France) is different: 2 rows.
  2. 2. A homeroom column contains A, B, NULL and NULL. What do SELECT DISTINCT homeroom and SELECT count(DISTINCT homeroom) return?

    DISTINCT keeps one NULL (A, B, NULL: 3 rows), but count skips NULLs and counts only A and B.
  3. 3. What does SELECT DISTINCT(title), first_name FROM employees do?

    The parentheses only wrap the expression title. DISTINCT always applies to the whole selected row.
  4. 4. You need each order status together with the number of orders that have it. What should you use?

    DISTINCT only lists values. To compute a number per value you need GROUP BY.

Summary

  • SELECT DISTINCT removes duplicate rows from the result; SELECT ALL (the default) keeps them.
  • DISTINCT compares the whole selected row, so with several columns you get distinct combinations. It is not a function.
  • DISTINCT keeps one NULL, but count(DISTINCT col) ignores NULLs.
  • count(DISTINCT col) counts different values; sum/avg/group_concat accept DISTINCT too.
  • With SELECT DISTINCT, sort only by selected columns (or use GROUP BY and sort by a summary).
  • Use GROUP BY when you need numbers per value, and don't use DISTINCT to hide duplicates caused by joins.
  • For one whole row per group, use PostgreSQL's DISTINCT ON or row_number() elsewhere.

Next: Column and Table Aliases, where you'll give columns and tables clearer names.