Level 4 · List the category tree

Many products share the same category and subcategory, so selecting those two columns returns lots of repeated rows. DISTINCT removes duplicate rows from the result:

SELECT DISTINCT category
FROM products;

This returns each category once. Harbor & Pine sells six: Furniture, Lighting, Kitchen, Textiles, Decor and Outdoor.

DISTINCT applies to the whole row

DISTINCT is not a function of one column. It looks at the entire row you selected:

SELECT DISTINCT category, subcategory
FROM products;

returns each unique pair. “Furniture / Sofas” and “Furniture / Tables” are different rows, so both appear, and “Furniture” shows up as many times as it has subcategories. Writing SELECT DISTINCT(category), subcategory does not change this; the parentheses are just ignored grouping around one column.

How analysts use it

• Profiling a new table. “What values does status take?” is answered by SELECT DISTINCT status FROM orders;. It is the fastest way to discover that a column contains 'Completed', 'completed' and 'completed ' as three different values.
• Building lookup lists for filters, dropdowns or a mapping sheet.
• Checking a key. If SELECT DISTINCT email FROM customers returns fewer rows than the table has, some emails repeat. You will chase exactly that problem in chapter 7.

NULL counts as a value here

If a column contains NULLs, DISTINCT keeps one NULL row. For example SELECT DISTINCT referral_source FROM customers; includes a NULL row for customers with no recorded source. That is one of the few places where SQL treats two NULLs as “the same”.

Common mistakes

• Using DISTINCT to hide a bug. If a join produces duplicate rows, slapping DISTINCT on top makes the output look right while sums and counts elsewhere are still wrong. Find out why rows repeat first. Chapter 5 covers this “fan-out” problem in depth.
• Expecting a sorted result. DISTINCT often comes back sorted by accident because of how the database removes duplicates, but that is not guaranteed. Add ORDER BY when order matters.
• Counting unique values. To get the number of unique categories, use COUNT(DISTINCT category), covered in chapter 4.