By the end of this lesson you can use SELECT DISTINCT on one or several columns, predict which rows it keeps (including NULL and inconsistently typed values), and recognise when DISTINCT is the wrong tool.
Why it matters: "Which cities do we deliver to?", "Which payment methods do customers use?" Listing the distinct values of a column is the first thing analysts do with an unfamiliar table, and it is the fastest way to discover messy data.
Dataset: Wadi Market online store. Examples run in the editor on the right.
Wadi Market has more than six thousand orders but delivers to only a dozen cities. SELECT delivery_city FROM orders returns one row per order, so each city appears hundreds of times. Adding DISTINCT right after SELECT tells PostgreSQL to remove duplicate rows from the result, so each different value appears once.
SELECT DISTINCT column_a, column_b
FROM table_name;SELECT DISTINCT delivery_city
FROM orders;PostgreSQL reads every order, keeps only delivery_city, then throws away repeated values. Thousands of rows shrink to 12, one per city. The order of the rows is not guaranteed; add ORDER BY when you need a sorted list.
DISTINCT compares values exactly
Two values are duplicates only if they are exactly equal. For text that means same letters, same case, same spaces. That makes DISTINCT an excellent data-quality tool: list the distinct values of a column typed by humans and the inconsistencies jump out.
The customers.city column is typed by customers at signup. Run the next example and look closely at the result: you'll find a city in capitals, another in lowercase, and some with a leading or trailing space (look at where they sort). To DISTINCT, 'Jeddah', 'JEDDAH' and 'Jeddah ' are three different cities. You'll learn to clean them in the last lesson of this track.
NULL gets special treatment: for DISTINCT, all NULLs count as the same, so the result contains a single NULL row standing for city unknown.
SELECT DISTINCT city
FROM customers
ORDER BY city;About twenty values come back, sorted so look-alikes sit near each other: Buraidah next to BURAIDAH, riyadh at the end (in this database lowercase letters sort after capitals) and values with a leading space at the very top. The single NULL row at the bottom represents every customer without a city.
DISTINCT on several columns
With more than one column, DISTINCT keeps each different combination. SELECT DISTINCT channel, status returns one row for every channel and status pair that actually occurs, such as app, delivered and web, cancelled. A value can therefore appear several times in one column, as long as the full row is different.
Because DISTINCT works on the whole row, you can't apply it to a single column in the middle of a list: SELECT order_id, DISTINCT channel is a syntax error. And adding a column that is unique per row (like order_id) makes every row distinct, so nothing gets removed.
SELECT DISTINCT channel, status
FROM orders
ORDER BY channel, status;Each row is a channel–status pair that appears in at least one order. channel repeats down the first column because it pairs with several statuses. If some combination is missing (say, no marketplace order is currently pending), it simply doesn't appear.