Lesson 04 of 61

5 mins read

SQL SELECT DISTINCT & Row Deduplication

In Plain English

The `SELECT DISTINCT` clause eliminates duplicate rows from the query output, returning only unique values or combinations. `DISTINCT` operates across all projected columns simultaneously.

The Mental Model

"If a guest list has someone's name written five times because they RSVP'd repeatedly, `SELECT DISTINCT` hands you a deduplicated list with their name appearing exactly once."

Deep Dive: How It Works

Multi-Column DISTINCT: `SELECT DISTINCT country, city FROM customers` evaluates uniqueness on the COMBINATION of country and city together.

Performance Impact: Deduplication requires sorting or building a hash set of results, adding memory and CPU overhead to large queries.

COUNT(DISTINCT col): Calculates the cardinality (number of unique non-null values) in a dataset.

Syntax Blueprint

SELECT DISTINCT column1, column2
FROM table_name;

DISTINCT evaluates across all columns listed in the SELECT clause.

Core Rules to Remember

Evaluates All Columns: `SELECT DISTINCT a, b` returns unique (a, b) tuples, not just distinct a values.

NULL Handling: Multiple NULLs are treated as duplicates of each other; DISTINCT returns a single NULL row.

Cardinality Inspection: Use `COUNT(DISTINCT category)` to find how many unique categories exist in a table.

Common Beginner Traps & How to Fix Them

Mistake:Writing `SELECT DISTINCT(col1), col2` thinking DISTINCT applies only to col1.

Why it happens: DISTINCT is a clause keyword modifying the entire row projection, not a function.

How to fix: To group by one column and pick one arbitrary value from another, use `GROUP BY`.

Pro Tip: Always verify if an existing index covers the distinct columns; indexed B-Trees can satisfy DISTINCT without sorting.

Live Interactive Example

Hit Run Code to see it live

Querying Unique Categories and Multi-Column Pairs

Python 3.12

Output Console

Click "Run Code" to view the rendered output.

How it works: DISTINCT reduced the 5 orders down to 2 distinct countries and 3 unique country/city combinations.

Your Turn: Micro Challenge

No pressure! Edit the starter code below and test your solution with instant feedback.

Micro Exercise

Select Unique Departments

Query distinct department names from the `staff` table.

Sandbox Output

Click "Run & Check" to test your solution.

Finished reading and practicing?

Mark this lesson as completed to update your course progress.