DISTINCT

Objectives

By the end of this lesson, you should be able to:

• Remove duplicate rows from a result set with DISTINCT
• Explain how DISTINCT behaves with more than one column

💡 Why this matters: Real tables repeat values constantly, many products share a category, many orders share a status. DISTINCT answers “what are the different values here?” without manually scanning for duplicates yourself.

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database, using the products table from the last lesson.

Removing Duplicates

products has 10 rows, but only three different category values, Electronics shows up four times, Home three times, Office three times. DISTINCT collapses that down to just the unique values:

SELECT DISTINCT category FROM products;

  category
-------------
 Electronics
 Home
 Office

Without DISTINCT, SELECT category FROM products; would return 10 rows, one per product, with Electronics repeated four times. DISTINCT reduces that to exactly the 3 different values that actually occur.

DISTINCT with Multiple Columns

DISTINCT applies to the whole row being selected, not just one column. Selecting two columns with DISTINCT returns every unique combination of both:

SELECT DISTINCT category, in_stock FROM products;

This would return one row per unique (category, in_stock) pair, ('Electronics', true) and ('Electronics', false) count as two different combinations, even though they share the same category.

Try It

1. Write a query that returns every distinct category in products.
2. Without running it, predict how many rows SELECT DISTINCT in_stock FROM products; would return, then run it to check.
3. Explain, in your own words, why SELECT DISTINCT category, in_stock FROM products; might return more rows than SELECT DISTINCT category FROM products; alone.

Recap

• DISTINCT removes duplicate rows from a result set, based on the exact combination of columns selected.
• SELECT DISTINCT one_column returns each unique value in that column, once.
• SELECT DISTINCT with multiple columns returns each unique combination of those columns, not each column’s unique values separately.

Next lesson: column aliases, renaming a column in a result set with AS.