DISTINCT

Definition

DISTINCT removes duplicate rows from a SELECT's result, keeping only one copy of each unique combination of the selected columns.

Running example — a products(id, name, category, price, stock) table, where stock can be NULL (not yet counted):

sql
SELECT DISTINCT category FROM products;

This returns each category name exactly once, no matter how many products share it — 'Electronics', 'Stationery', 'Books', etc., each appearing a single time.

Edge Cases and Pitfalls

• DISTINCT applies to the ENTIRE row of selected columns, not each column independently: SELECT DISTINCT category, price FROM products keeps a row only if the (category, price) COMBINATION is unique, not each column separately.
• DISTINCT requires the database to compare and de-duplicate every row, which has a real performance cost on large result sets — it's not "free."
• COUNT(DISTINCT column) (combining DISTINCT with an aggregate, as seen in Chapter 14) is a different syntactic position than SELECT DISTINCT — both achieve de-duplication, but in different contexts (one counts distinct values, the other returns distinct rows).

Key Takeaways

• DISTINCT removes duplicate rows based on the full combination of selected columns.
• It has a genuine performance cost — a de-duplication pass over the result.
• SELECT DISTINCT col and COUNT(DISTINCT col) are related but distinct usages.