DISTINCT

Concept

Sometimes a database table contains duplicate data points. For example, in an orders table containing 10,000 transactions, the city column will likely say “New York” thousands of times.
If you just want a list of all the unique cities your customers live in, you use the DISTINCT keyword. It forces the database to evaluate the result set and remove any duplicate rows before returning it to you.

Basic Syntax

The DISTINCT keyword immediately follows the SELECT command.

-- Returns a list of unique cities (e.g., 50 rows)
SELECT DISTINCT city 
FROM orders;

DISTINCT on Multiple Columns

When you apply DISTINCT to multiple columns, it evaluates the entire combination of the columns to determine uniqueness.

-- Returns unique combinations of City + State
SELECT DISTINCT city, state 
FROM orders;

If your data has ('Springfield', 'IL') and ('Springfield', 'MA'), both rows will be returned because the combination is distinct.

DISTINCT vs GROUP BY

You can almost always rewrite a DISTINCT query using a GROUP BY query. They often achieve the exact same result.

-- These two queries do the exact same thing
SELECT DISTINCT city FROM orders;
SELECT city FROM orders GROUP BY city;

Which one should you use?

• Use DISTINCT when your only goal is to deduplicate a list for presentation. It is explicitly designed for readability.
• Use GROUP BY if you plan to eventually add math (Aggregate Functions) to the query, like counting how many orders occurred in each unique city.

Trade-Offs

DISTINCT is a heavy, computationally expensive operation.
To remove duplicates, the database must internally sort the entire result set or build a hash table in RAM to identify matches. If you run SELECT DISTINCT on a massive 10-million row unindexed text column, the database will consume massive memory and CPU cycles.

Junior Developer Anti-Pattern:
Junior developers often write complex JOIN queries that accidentally duplicate data due to bad relationship mapping. Instead of fixing the bad JOIN logic, they lazily slap a DISTINCT at the top of the query to hide the duplicates. This forces the database to process millions of duplicated rows, only to violently discard them at the very end, crippling performance. Never use DISTINCT as a band-aid for bad JOINs.

Interview Questions

Q: Look at this query: SELECT COUNT(DISTINCT city) FROM orders;. How does this differ from SELECT DISTINCT COUNT(city) FROM orders;?
A: They do entirely different things.

1. COUNT(DISTINCT city) is standard and correct. It looks at the column, removes all duplicate cities, and then counts how many unique cities remain. Result: 50.
2. DISTINCT COUNT(city) is a logic error. It first executes the aggregation, counting all rows with a city. That returns exactly 1 row (e.g., 10000). Then, it applies DISTINCT to that single row. Since it’s only 1 row, it does nothing. Result: 10000.

Q: You want to find unique cities, but you ALSO want to return the order_id associated with them. You write: SELECT DISTINCT city, order_id FROM orders. The query fails to deduplicate the cities. Why?
A: DISTINCT applies to the entire row combination. Because order_id is a primary key (1, 2, 3…), every single row is inherently unique. Therefore, DISTINCT cannot deduplicate anything, and it returns all 10,000 rows.
If you want a list of unique cities, but also want an associated order ID (maybe the most recent one), you cannot use DISTINCT. You must use GROUP BY city alongside an aggregate function like MAX(order_id), or use a Window Function like ROW_NUMBER().