SQL SELECT DISTINCT: Remove Duplicate Rows

Without DISTINCT, every row comes back, duplicates included. Compare the number of order rows with the number of different statuses:

SELECT COUNT(status) AS status_values,
       COUNT(DISTINCT status) AS different_statuses
FROM orders;

▶ Run this query in the SQL Playground

DISTINCT lists those different values:

SELECT DISTINCT status
FROM orders
ORDER BY status;

▶ Run this query in the SQL Playground

SELECT DISTINCT column_1, column_2, ...
FROM table
[WHERE ...]
[ORDER BY ...];

DISTINCT goes right after SELECT and applies to the whole row of selected columns. It is removed from the result after the rows are found; it does not change how they are read from the table.

With more than one column, a row is a duplicate only if every selected column matches. Each department and city where employees work:

SELECT DISTINCT department, city
FROM employees
WHERE department = 'Engineering'
ORDER BY city;

▶ Run this query in the SQL Playground

Engineering appears on several rows because it has employees in several cities. Each department and city pair appears once.

COUNT(DISTINCT column) counts different values instead of rows. How many customers have ordered, compared with how many orders there are:

SELECT COUNT(*) AS orders,
       COUNT(DISTINCT user_id) AS customers_who_ordered,
       COUNT(DISTINCT shipping_city) AS cities
FROM orders;

▶ Run this query in the SQL Playground

It works per group too. Distinct customers per order status:

SELECT status,
       COUNT(*) AS orders,
       COUNT(DISTINCT user_id) AS customers
FROM orders
GROUP BY status
ORDER BY orders DESC;

▶ Run this query in the SQL Playground

SQLite and SQL Server accept only one column inside COUNT(DISTINCT ...). To count distinct pairs, count the rows of a DISTINCT subquery: SELECT COUNT(*) FROM (SELECT DISTINCT department, city FROM employees). MySQL and PostgreSQL also accept COUNT(DISTINCT department, city) or a row value.

For DISTINCT, all NULLs count as the same value, so NULL appears at most once in the result. COUNT(DISTINCT ...) leaves NULL out entirely:

SELECT COUNT(DISTINCT manager_id) AS distinct_managers,
       (SELECT COUNT(*) FROM (SELECT DISTINCT manager_id FROM employees)) AS distinct_rows_including_null
FROM employees;

▶ Run this query in the SQL Playground

There are 10 different managers. SELECT DISTINCT returns one more row, the single NULL for employees who have no manager.

For a plain list of unique values, these two queries return the same rows:

SELECT category
FROM products
GROUP BY category
ORDER BY category;

▶ Run this query in the SQL Playground

SELECT DISTINCT category
FROM products
ORDER BY category;

Use DISTINCT when you only want the list; use GROUP BY as soon as you want a number per value.

DISTINCT is applied before ORDER BY and LIMIT, so LIMIT 5 gives five different values:

SELECT DISTINCT shipping_city
FROM orders
ORDER BY shipping_city
LIMIT 5;

▶ Run this query in the SQL Playground

Most databases only let you ORDER BY columns that are in the SELECT DISTINCT list. Sorting by a column that is not selected would be ambiguous: which of the duplicate rows should decide the position?

Five errors and how to avoid them.

SELECT DISTINCT department, first_name
FROM employees;

SELECT DISTINCT department
FROM employees;

SELECT DISTINCT COUNT(user_id)
FROM orders;

SELECT COUNT(DISTINCT user_id)
FROM orders;

SELECT DISTINCT u.id, u.first_name
FROM users u
JOIN orders o ON o.user_id = u.id;

SELECT u.id, u.first_name
FROM users u
WHERE EXISTS (SELECT 1 FROM orders o
              WHERE o.user_id = u.id);

SELECT UNIQUE category FROM products;

SELECT DISTINCT category FROM products;

SELECT DISTINCT id, email
FROM users;

SELECT id, email
FROM users;

5 exercises on the real Playground tables.

Write each query yourself first. Every solution has been run against the Playground database.

1. 1Easy List every department name once, in alphabetical order. Table: employees

   Show solution

   SQLite · runs in the Playground

   SELECT DISTINCT department
   FROM employees
   ORDER BY department;

   ▶ Run this query in the SQL Playground

   DISTINCT removes the repeats; ORDER BY sorts them.

2. 2Easy How many different countries do users come from? Table: users

   Show solution

   SQLite · runs in the Playground

   SELECT COUNT(DISTINCT country) AS countries
   FROM users;

   ▶ Run this query in the SQL Playground

   COUNT(DISTINCT ...) counts each country once.

3. 3Medium List each payment method together with each status it has had. Table: payments

   Show solution

   SQLite · runs in the Playground

   SELECT DISTINCT method, status
   FROM payments
   ORDER BY method, status;

   ▶ Run this query in the SQL Playground

   With two columns, each method and status pair appears once.

4. 4Medium How many different products have been ordered, and in how many different orders? Table: order_items

   Show solution

   SQLite · runs in the Playground

   SELECT COUNT(DISTINCT product_id) AS products_ordered,
          COUNT(DISTINCT order_id) AS orders
   FROM order_items;

   ▶ Run this query in the SQL Playground

   Two COUNT(DISTINCT ...) in one query, each on its own column.

5. 5Medium For each department, count the different cities its employees work in. Table: employees

   Show solution

   SQLite · runs in the Playground

   SELECT department, COUNT(DISTINCT city) AS cities
   FROM employees
   GROUP BY department
   ORDER BY cities DESC, department;

   ▶ Run this query in the SQL Playground

   COUNT(DISTINCT city) runs once per department group.