Skip to main content
SQL Fundamentals
4 / 5
0%

DISTINCT — unique values

0/4 solved4 to unlock next
PrevLocked

Lesson 04

DISTINCT — unique values

Lesson Description

Remove duplicates with DISTINCT and choose the correct uniqueness grain for the question being asked. You will learn when DISTINCT is the right tool and when grouping logic is a better fit.

Easy Project

Mini project: Turn this lesson into a real-world query artifact by writing one clean business report query and validating output quality.

The problem DISTINCT solves

Many questions are about which values exist, not about individual rows. "Which cities do our patients come from?" "Which customers have placed at least one order?" "How many countries do we sell to?" If you simply select the column, you get one row per record in the table, full of repeats. The hospital has 30 patients but only a handful of cities, so SELECT city FROM patients gives you 30 rows with Seattle appearing again and again.

DISTINCT removes duplicate rows from the result, so each unique value (or combination of values) appears exactly once. It is one of the quickest tools for exploring an unfamiliar table and for checking how many different values a column really holds.

Syntax

SELECT DISTINCT column1, column2
FROM table_name;

DISTINCT goes directly after SELECT and applies to the whole row of output, not just the first column. There is also a counting form:

SELECT COUNT(DISTINCT column1)
FROM table_name;

Worked examples

Example 1: unique values in one column

SELECT DISTINCT city
FROM patients
ORDER BY city;

Instead of 30 rows, you get 5: Bellevue, Portland, Seattle, Spokane and Tacoma. Without DISTINCT, Seattle alone would appear 10 times, because 10 of the 30 patients live there.

This is often the first thing an analyst runs against a new table. Before writing a filter like WHERE city = 'Seattle', you want to know how the values are actually spelled.

The same check on the store's orders table tells you a lot about the business:

SELECT DISTINCT status
FROM orders
ORDER BY status;

Four statuses exist: cancelled, delivered, pending and shipped. If someone asks you to report "returned" orders, you now know there is no such status in the data.

Example 2: who did something at least once

Each order row has a customer_id. Selecting it plainly gives you 50 rows, one per order, with repeat buyers listed several times. DISTINCT turns it into a list of customers who have ordered:

SELECT DISTINCT customer_id
FROM orders
ORDER BY customer_id;

That returns 20 rows. The customers table has 22 people, so you have just learned that 2 customers signed up but never placed an order. That kind of quick insight is where DISTINCT shines. (Finding exactly which 2 is a job for joins, later in the curriculum.)

Example 3: DISTINCT on a combination of columns

With several columns, DISTINCT keeps one row for each unique combination. In the hospital:

SELECT DISTINCT city, gender
FROM patients
ORDER BY city, gender;

This returns 9 rows. Bellevue, Seattle, Spokane and Tacoma each appear twice (once for F and once for M), but Portland appears only once, with F. You have discovered that every Portland patient in the sandbox is female, something you would not see from either column alone.

The store sandbox has a good example of why the combination matters. Look closely at the products:

QueryRows
SELECT name FROM products27
SELECT DISTINCT name FROM products26
SELECT DISTINCT name, price FROM products26
SELECT DISTINCT name, stock FROM products27

There are two products called "Yoga Mat" (ids 23 and 26), both priced 29.99 but with different stock levels (100 and 40). By name, or by name and price, they collapse into one row. Add stock and they become different combinations again, so both rows come back. Every column you add to a DISTINCT list can only keep the row count the same or make it larger.

Example 4: counting distinct values

Often you want the number of unique values, not the list. COUNT(DISTINCT ...) does that in one step:

SELECT
  COUNT(*)                AS customers,
  COUNT(DISTINCT country) AS countries
FROM customers;

The answer is a single row: 22 customers spread across 11 countries. COUNT(*) counts rows; COUNT(DISTINCT country) counts different country values. You will meet COUNT and the other aggregate functions properly in the Aggregations course; for now, treat COUNT(DISTINCT col) as "how many unique values".

Example 5: one row per group with DISTINCT ON

Sometimes you want one complete row for each distinct value, for example "the most recently hired employee in each department". Plain DISTINCT cannot do that, because adding names and dates to the list makes every row unique. PostgreSQL has an extension for exactly this case:

SELECT DISTINCT ON (department_id)
  department_id, first_name, last_name, hire_date
FROM employees
ORDER BY department_id, hire_date DESC;

You get 9 rows, one per department that has employees. For department 80 (Sales) it is Eleni Zlotkey, hired 2008-01-29; for department 100 (Finance) it is Luis Popp, hired 2007-12-07.

The rule is: DISTINCT ON (x) keeps the first row for each value of x, and "first" is decided by ORDER BY. The ORDER BY must start with the same column(s) as DISTINCT ON, and the columns after that decide which row wins. Here hire_date DESC makes the newest hire win. Change it to hire_date ASC and you get the longest-serving person in each department instead.

Under the hood

In the logical order you have been following (FROM, WHERE, SELECT, DISTINCT, ORDER BY, LIMIT), DISTINCT runs after the output columns are computed and before sorting. Two consequences:

  • DISTINCT compares the values you selected, including calculated columns and aliases. It never looks at columns you left out.
  • When you use DISTINCT, ORDER BY can only use columns that are in the output, because the other columns no longer exist as a single value per row. (Which patient's last name should PostgreSQL use to sort the row "Seattle"?)

DISTINCT and NULL

For DISTINCT, all NULLs count as the same value, so they collapse into one row. In HR, SELECT DISTINCT manager_id FROM employees returns 10 rows: 9 real manager ids plus one NULL (Steven King has no manager). But COUNT(DISTINCT manager_id) returns 9, because COUNT of a column ignores NULLs. Same data, two different numbers. Keep that in mind when you compare a list with a count.

To find duplicates, PostgreSQL usually either sorts the rows or builds a hash table of them. On large tables that work is not free, so add DISTINCT because you need it, not as a reflex.

Common mistakes

Thinking DISTINCT applies to one column

SELECT DISTINCT (first_name), last_name
FROM employees;

The parentheses look like they limit DISTINCT to first_name, but they are just grouping one expression. DISTINCT still applies to the whole row. There are 35 distinct first names in HR (Alexander, David, John, Karen and Peter each appear twice), but this query returns all 40 rows, because every first and last name pair is unique. If you want one row per first name with some other column attached, you need DISTINCT ON or GROUP BY.

Ordering by a column that is not selected

SELECT DISTINCT city
FROM patients
ORDER BY last_name;

PostgreSQL refuses with for SELECT DISTINCT, ORDER BY expressions must appear in select list. Sort by city, or add the sort column to the list (which changes what counts as distinct).

Using DISTINCT to hide a bug

When a query that joins tables returns duplicates you did not expect, adding DISTINCT makes the symptom disappear, but the cause (usually a join on the wrong column) is still there and can make totals wrong elsewhere. You will see this in the Joins course. Use DISTINCT when duplicates are a real feature of the data, not to paper over a mistake.

How other databases differ

  • SELECT DISTINCT and COUNT(DISTINCT col) work in MySQL, SQL Server, SQLite and every other major database.
  • DISTINCT ON is PostgreSQL only. Elsewhere you get the same result with a window function such as ROW_NUMBER(), covered in the Window Functions course.
  • MySQL allows COUNT(DISTINCT a, b) across several columns. PostgreSQL does not accept that form, but you can wrap the columns in parentheses to count distinct pairs: COUNT(DISTINCT (city, gender)) returns 9 for the patients table, matching Example 3.

Practice

The exercises below this lesson ask you to list unique values from HR and store tables, count how many different doctor specialties exist in the hospital, and find unique pairs of two columns. Remember to sort the output when the prompt asks for it, and think about whether you need the list of values or just how many there are.

Key takeaways

  • SELECT DISTINCT removes duplicate output rows; it considers every selected column together.
  • Adding columns to a DISTINCT list can only keep the row count the same or increase it.
  • COUNT(DISTINCT col) counts unique non-NULL values, while DISTINCT lists NULL as one value.
  • With DISTINCT, ORDER BY may only use selected columns.
  • DISTINCT ON (col) (PostgreSQL only) keeps one full row per value, chosen by ORDER BY.

DISTINCT — unique values: in depth

DISTINCT collapses duplicate rows into one, which answers questions like "which departments have staff" or "which countries do customers come from". It applies to the whole selected row, so it is a tool for listing unique combinations, not for fixing queries that return too many rows.

Worked examples

Unique values in one column

SELECT DISTINCT country
FROM customers
ORDER BY country;

One row per country, regardless of how many customers live there.

Unique combinations

SELECT DISTINCT department_id, job_id
FROM employees
ORDER BY department_id, job_id;

DISTINCT considers both columns together, so each department/job pairing appears once.

Common mistakes

  • Adding DISTINCT to hide duplicates created by a bad join. The numbers in other columns are still wrong; fix the join instead.
  • Expecting DISTINCT to apply to one column only. SELECT DISTINCT country, first_name returns unique pairs, not unique countries.
  • Using DISTINCT when you need counts. To know how many customers per country, use GROUP BY with COUNT(*).

Other databases

  • PostgreSQL also has DISTINCT ON (col), which keeps the first row per value according to ORDER BY. Other databases need ROW_NUMBER() for the same result.
  • NULLs are treated as equal by DISTINCT in every major database, so all NULLs collapse into one row.
Check yourself: What does SELECT DISTINCT department_id FROM employees return if three employees have no department?

One row with NULL alongside the real department ids. DISTINCT groups all NULLs together.

Concept check

Quick check: DISTINCT — unique values

3 quick questions. One at a time. Instant score at the end.

Exercise 1

Unique department IDs

Enterprise Workforce AnalyticseasyPostgreSQL

Return the distinct department_id values from the employees table, ordered ascending.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Exercise 2

Unique product category IDs

Commerce Operations IntelligenceeasyPostgreSQL

From the store sandbox, return the distinct category_id values from the products table, ordered ascending.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Exercise 3

Count distinct doctor specialties

Clinical Care PerformanceeasyPostgreSQL

From the hospital sandbox, return a single value specialty_count — the number of distinct specialties across all doctors.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Exercise 4

Unique department and job combinations

Enterprise Workforce AnalyticseasyPostgreSQL

Return distinct (department_id, job_id) pairs from the employees table, ordered by department_id, then job_id.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

SQL Fundamentals
4 / 5
0%

DISTINCT — unique values

0/4 solved4 to unlock next
PrevLocked