Level 6 · Which coupons show up on which channel?

SELECT DISTINCT was introduced on the category tree. This ticket applies it to a much bigger table, orders, and adds a column that is often empty.

DISTINCT looks at the whole row

DISTINCT goes right after SELECT and applies to the combination of every column you list, not to the first one only. On one column it gives the list of values in use:

SELECT DISTINCT coupon_code
FROM orders;

That returns 7 rows: the six codes (BFCM25, FREESHIP, HOLIDAY20, SPRING15, SUMMER15, WELCOME10) plus one NULL for orders placed without a coupon. Add channel and the database keeps one row per pair that actually occurs. On Harbor & Pine’s data every code has been used on every channel, so the pairs come to 3 × 6 = 18, plus three no-coupon rows, 21 in total.

If a combination never happened, it simply is not in the result. That is the point of the ticket: the list shows what exists, and anything missing is a finding.

NULL in a DISTINCT

Elsewhere in SQL, NULL is not equal to anything, not even another NULL. DISTINCT is an exception: it treats all NULLs in a column as the same value. So the 10,277 orders with no coupon collapse into one row per channel, not thousands, and not zero. GROUP BY follows the same rule, which you will meet in chapter 4.

Common mistakes

• Writing DISTINCT in front of one column in the middle of the list, like SELECT channel, DISTINCT coupon_code. That is a syntax error. DISTINCT belongs right after SELECT and covers the whole row.
• Adding an id column “for reference”. SELECT DISTINCT order_id, channel, coupon_code is unique on every row, so you get all 12,000 rows back.
• Treating the result as counts. DISTINCT tells you which pairs exist, not how often. Counting them per pair is a GROUP BY job.

In SQL Server

Same syntax, and NULLs are grouped the same way. SQL Server lets you wrap any name in square brackets, which you need when a name has a space or clashes with a keyword:

SELECT DISTINCT [channel], [coupon_code]
FROM [orders];