DISTINCT - SQL

1. Which query returns the same rows as SELECT DISTINCT c FROM t?
   Answer: SELECT c FROM t GROUP BY c Grouping by c collapses duplicate values into one row per distinct c, so it returns the same unique c values as SELECT DISTINCT c. The tempting trap is ORDER BY c: it sorts the rows but does not remove duplicates, so it returns extra rows. SELECT s or SELECT c,s also change the columns, so they do not match.
2. Which result pair is produced by these two scalar queries, in the order shown? SELECT COUNT(DISTINCT email) FROM contacts; SELECT COUNT(*) FROM (SELECT DISTINCT email FROM contacts) d;
   Answer: First query returns 2; second query returns 3 When working with COUNT(DISTINCT ...) versus SELECT DISTINCT, the critical distinction is how each handles NULL values — and this question is designed specifically to test that. COUNT(DISTINCT email) ignores NULLs entirely before deduplicating. From your five values — 'a@example.com', 'a@example.com', NULL, NULL, 'b@example.com' — the NULLs are discarded first, leaving 'a@example.com' and 'b@example.com'. After deduplication, that's 2 distinct non-null values, so the first query returns 2. The second query does something subtly different. SELECT DISTINCT email deduplicates all values, including NULLs. In SQL, DISTINCT treats all NULLs as a single group, so your five rows collapse into three distinct "values": 'a@example.com', 'b@example.com', and NULL. The outer COUNT(*) then counts rows — not column values — so NULLs are included in the row count, returning 3. That makes A the correct answer. B is wrong because it assumes both queries behave identically, giving 3 each — but COUNT(DISTINCT ...) never counts NULLs. C is wrong for the opposite reason: it assumes both queries exclude NULLs, but COUNT(*) counts every row in the subquery, including the NULL row. D is nonsensical — neither query can return 5, since deduplication always reduces the row count. As a study tip, remember this pattern: **COUNT(column) always skips NULLs, but COUNT(*) never does.** When a subquery surfaces NULLs as rows, wrapping it in COUNT(*) will count them.
3. SELECT DISTINCT dept, manager FROM emp; Rows are removed when they match on:
   Answer: The dept-manager pair DISTINCT applies to the entire select list, so rows are removed only when both dept and manager match a previous row. The tempting mistake is thinking it deduplicates each column separately, but that would drop rows with the same dept even if managers differ. Only identical dept-manager pairs are collapsed.
4. Which additional constraint is sufficient to guarantee that removing DISTINCT never changes this query's result?
   Answer: badges.employee_id is unique across all badge records When a JOIN produces duplicate rows, DISTINCT is doing real work — eliminating them. Your goal here is to find the constraint that makes duplicates impossible, so DISTINCT becomes redundant. Think about when duplicates arise in this query. You're selecting (employee_id, department_id) pairs after joining employees to badges. Since employee_id is already a primary key in employees, each employee has exactly one department_id. The only way a duplicate output row can appear is if a single employee matches multiple rows in badges — because each match produces another copy of that employee's (employee_id, department_id) pair. Option B is correct because making badges.employee_id unique across all badge records means each employee can appear in badges at most once. With at most one matching badge row per employee, the JOIN produces at most one output row per employee — so DISTINCT changes nothing. Option A is wrong because badge_id being unique says nothing about employee_id in badges. Multiple distinct badges could still belong to the same employee, causing duplicate output rows. Option C is wrong because a foreign key on employees.department_id only validates that departments exist — it has no effect on row duplication produced by the JOIN. Option D is wrong because employees.employee_id is already a primary key (unique and NOT NULL by definition), so this constraint adds nothing new and doesn't prevent one employee from having multiple badge rows. The strategy to remember: when DISTINCT is involved, ask yourself where duplicate rows originate. Trace the JOIN path — uniqueness on the joining column of the secondary table is usually what eliminates them.
5. Region has 40 NULLs, 30 'West', 30 'East'. SELECT DISTINCT region returns:
   Answer: 3 rows Three distinct groups exist: NULL, 'West', and 'East', so DISTINCT returns 3 rows. SQL treats every NULL as the same value for DISTINCT, so all 40 NULLs collapse into one row. The tempting answer 2 rows ignores NULL, but NULL is still a distinct returned group.
6. How many rows are returned by SELECT DISTINCT c.name FROM customers c JOIN orders o ON o.customer_id = c.customer_id;?
   Answer: One row, because all qualifying customers have the name Jordan When you see a JOIN combined with DISTINCT, you need to think in two separate stages: first, what rows does the JOIN produce, and second, what does DISTINCT do to those results? Start with the JOIN. An inner JOIN between customers and orders only returns rows where a match exists. Customer 3 (Casey) has no orders, so Casey is eliminated entirely. Customer 1 (Jordan) has two orders, producing two joined rows. Customer 2 (also Jordan) has one order, producing one joined row. So before DISTINCT, you have three rows — all with the name 'Jordan'. Now apply SELECT DISTINCT c.name. DISTINCT collapses duplicate values in the selected column. All three rows share the same name value, 'Jordan', so they collapse into a single row. The query returns one row. Answer A is correct for exactly this reason — only one unique name survives after both JOIN filtering and DISTINCT deduplication. Answer B is tempting because two customer records have orders, but DISTINCT operates on the output values, not the source rows. Both qualifying customers share the name 'Jordan', so they collapse to one. Answer C mistakes the pre-DISTINCT row count (three joined rows) for the final result, forgetting that DISTINCT is applied before anything is returned. Answer D is simply fabricated logic — there's no SQL behavior that multiplies customers and orders together this way. Study tip: Always trace a query in two mental steps — what does the JOIN produce, then what does SELECT (with any modifiers like DISTINCT) do to that result set?
7. How many rows are returned by SELECT DISTINCT customer_id, region FROM orders;?
   Answer: Four rows, because four distinct value pairs appear When you see SELECT DISTINCT applied to multiple columns, remember that distinctness applies to the entire combination of those columns — not to any single column in isolation. SQL evaluates each unique pairing of values across all listed columns together. Working through the six pairs — (1, 'East'), (1, 'East'), (1, 'West'), (2, 'East'), (2, 'East'), (2, 'West') — you can identify the unique combinations: (1, 'East'), (1, 'West'), (2, 'East'), and (2, 'West'). That's four distinct pairs, making C correct. The duplicate (1, 'East') and duplicate (2, 'East') rows are each collapsed to one, reducing six rows down to four. Choice A is wrong because DISTINCT doesn't deduplicate on customer_id alone. You're selecting two columns, so SQL considers both. Choosing only the two unique customer IDs would require SELECT DISTINCT customer_id with no region column. Choice B incorrectly counts three distinct region occurrences — but "East" and "West" are only two distinct region values, and three occurrences of "West" or "East" don't appear either way. This answer reflects confusion about what's being counted. Choice D describes the behavior of a plain SELECT without DISTINCT — without that keyword, yes, all six source rows would be returned individually. A useful rule of thumb: when DISTINCT follows SELECT, mentally list every full row combination the query projects, then remove duplicates. The number of unique complete rows is your answer — regardless of how many columns are involved.
8. id is the PRIMARY KEY. For which query can DISTINCT still change results?
   Answer: SELECT DISTINCT dept FROM t Any query that includes the primary key id cannot have duplicate rows, so DISTINCT cannot change results. Only SELECT DISTINCT dept can merge rows that share the same dept. The tempting SELECT DISTINCT id, dept still includes id, so each row remains unique even though dept values repeat.
9. How many rows are returned by SELECT DISTINCT COUNT(*) AS ticket_count FROM tickets GROUP BY queue_id;?
   Answer: Two rows, containing the distinct counts 2 and 3 When a query uses both GROUP BY and DISTINCT with an aggregate function, you need to think in two stages: what does GROUP BY produce first, and then what does DISTINCT filter down to? Here, GROUP BY queue_id runs first and produces three intermediate rows — one per queue — with COUNT(*) values of 2, 2, and 3. Then DISTINCT collapses duplicate values in that result set. Since two queues both have a count of 2, those two rows reduce to one. The queue with count 3 stays as its own row. You're left with two distinct rows: one showing ticket_count = 2 and one showing ticket_count = 3, confirming B is correct. A is wrong because COUNT(*) being a single aggregate expression doesn't override GROUP BY — grouping still produces multiple rows before DISTINCT is applied. The confusion here is conflating "one expression" with "one output row." C would be correct without DISTINCT — yes, three groups produce three rows, but DISTINCT deduplicates the results afterward, collapsing the two identical counts of 2 into one. D misunderstands what COUNT(*) returns. The query never returns individual ticket rows; it returns aggregated counts per group. The source tickets (7 total) are consumed by the aggregation and never directly appear as output rows. Study tip: Train yourself to mentally execute SQL in clause order — FROM → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY. DISTINCT runs after aggregation, so always ask "what rows exist at that point?"
10. What is the key problem with using SELECT DISTINCT account_id, status FROM account_status; to return each account's latest status?
    Answer: It may return multiple statuses without choosing the latest timestamp When working with historical tables, always ask yourself: what does this query actually return, and does it guarantee the "latest" record? DISTINCT simply removes duplicate rows — it knows nothing about which row is most recent. Consider account 10: it has ('Active', '2026-01-01') and ('Suspended', '2026-03-01'). Since the status values differ, SELECT DISTINCT account_id, status returns both rows — giving you two statuses for one account, with no way to know which is current. This is exactly why D is correct: the query may return multiple statuses per account without ever consulting the timestamp to determine the latest one. The wrong answers reflect common misconceptions worth clearing up. A is incorrect because account 20 has two rows with status = 'Active' — those two rows are identical on the selected columns, so DISTINCT does collapse them into one. The account isn't removed; it's correctly deduplicated in this particular case. B is a fabricated behavior — DISTINCT applies no alphabetical or ranking logic whatsoever; it just suppresses exact duplicate rows. C is the opposite of reality: timestamps are not implicitly selected here, which is precisely the problem. Without changed_at in the query, you can't determine recency at all. The correct way to retrieve the latest status per account is to use ROW_NUMBER() with PARTITION BY account_id ORDER BY changed_at DESC, or a subquery with MAX(changed_at) joined back to the table. Study tip: Whenever a question involves "latest," "most recent," or "current" records, DISTINCT alone is never the right tool — you need an ordering or aggregation strategy.
11. How many rows are returned by SELECT DISTINCT a, b FROM samples;?
    Answer: Four rows, because four distinct projected pairs remain When you see SELECT DISTINCT applied to multiple columns, remember that SQL treats the combination of values as the unit of uniqueness — not each column independently. The key nuance here is how DISTINCT handles NULL: SQL considers two NULLs as duplicates of each other for the purpose of DISTINCT (even though NULL = NULL evaluates to UNKNOWN in a WHERE clause). Let's walk through the seven rows and identify the unique (a, b) pairs: - (NULL, 'X') — appears twice → counts as one distinct row - (NULL, 'Y') — appears once → one distinct row - (1, NULL) — appears twice → counts as one distinct row - (1, 'X') — appears twice → counts as one distinct row That gives you four distinct pairs, confirming answer C is correct. Answer A is wrong because it claims rows with NULL are discarded entirely. DISTINCT never discards rows based on NULL presence — it deduplicates them just like any other value. Answer B is wrong in the opposite direction: it claims each NULL is treated as unique, so duplicate (NULL, 'X') rows would both survive. That's not how DISTINCT works — identical pairs (even those containing NULL) are collapsed. Answer D is wrong because it confuses DISTINCT with a plain SELECT, which would indeed return all seven original rows without any deduplication. A helpful rule of thumb: for DISTINCT, think of NULL as a regular value for deduplication purposes only. Two rows with identical NULL-containing combinations will be collapsed into one.
12. What does the following query return? SELECT DISTINCT c.customer_id, o.status FROM customers c JOIN orders o ON o.customer_id = c.customer_id WHERE o.status = 'Shipped';
    Answer: Two rows: one Shipped row for each qualifying customer When you see both JOIN and DISTINCT in the same query, pause and think in two stages: first, what rows does the join produce? Second, what does DISTINCT collapse? Here, joining customers to orders on customer_id produces one row per matching order. Customer 1 has three orders (two Shipped, one Pending) and Customer 2 has one order (one Shipped). The WHERE o.status = 'Shipped' filter then removes the Pending row, leaving three rows: two Shipped rows for Customer 1 and one Shipped row for Customer 2. Now DISTINCT kicks in — it eliminates duplicate combinations of (customer_id, status). Customer 1's two Shipped rows are identical on those two columns, so they collapse into one. The final result is two rows: (1, 'Shipped') and (2, 'Shipped'), making A correct. Choice B is the most tempting trap — it reflects what the query would return without DISTINCT. Three rows exist after filtering, but DISTINCT merges the duplicates. Choice C ignores both the WHERE filter and DISTINCT, describing a raw join with no conditions applied — four rows if you count all three of Customer 1's orders plus Customer 2's one order. Choice D misreads DISTINCT as collapsing all rows into a single row, but DISTINCT removes duplicate combinations, not all uniqueness across customers. Your takeaway: always apply JOIN, then WHERE, then DISTINCT in that mental order. DISTINCT operates on the full selected column list as a unit — here (customer_id, status) together, not status alone.
13. How many rows are returned by SELECT DISTINCT LOWER(TRIM(code)) FROM items;?
    Answer: Three rows, representing ab, a b, and NULL When a question combines DISTINCT, NULL, and string functions, you need to trace each value through the transformations before counting distinct results. Start with the six raw values: ' Ab ', 'ab', 'AB', 'a b', NULL, NULL. Apply TRIM first to strip leading/trailing spaces, giving 'Ab', 'ab', 'AB', 'a b', NULL, NULL. Then apply LOWER, which converts letters but leaves spaces and NULLs untouched: 'ab', 'ab', 'ab', 'a b', NULL, NULL. Now DISTINCT collapses duplicates — the three 'ab' values merge into one, the two NULLs merge into one (SQL treats NULLs as duplicates for DISTINCT purposes), and 'a b' stands alone. That leaves exactly three distinct rows: 'ab', 'a b', and NULL, confirming B is correct. A is wrong because it claims all non-NULL codes normalize identically — but 'a b' has an internal space that TRIM does not remove, so it stays distinct from 'ab'. C is wrong because it suggests the original spellings survive transformation. The functions do collapse ' Ab ', 'ab', and 'AB' into one value, so four distinct rows is an overcount. D is wrong because it misunderstands how DISTINCT handles NULLs. DISTINCT does deduplicate NULLs into a single output row, not five rows. As a study habit, always walk through SQL transformations in pipeline order — function output feeds into DISTINCT — and remember that TRIM only targets edge spaces, never internal ones.
14. Score has 1,2,2,3,3,NULL. COUNT(DISTINCT score) returns:
    Answer: 3 COUNT(DISTINCT score) ignores NULLs, so only the non-null distinct values matter: 1, 2, and 3. That gives 3. The tempting mistake is to include NULL as a distinct value and answer 4, but NULL isn't counted by COUNT(DISTINCT).
15. Which priorities are returned, in order, by this query? SELECT DISTINCT priority FROM tasks ORDER BY priority OFFSET 1 ROW FETCH NEXT 2 ROWS ONLY;
    Answer: 2 and 3, because pagination follows distinctness and sorting When you see a query combining DISTINCT, ORDER BY, and OFFSET/FETCH, the key is understanding the logical order of operations: SQL processes DISTINCT and ORDER BY first, producing a clean, sorted result set, and then applies pagination (OFFSET/FETCH) to that final result. Here's how it plays out. The raw priority values are 1, 1, 2, 2, 2, 3, 4. After SELECT DISTINCT, duplicates are removed, leaving: 1, 2, 3, 4. After ORDER BY priority, the order is confirmed as 1, 2, 3, 4. Now pagination applies: OFFSET 1 ROW skips the first row (priority 1), and FETCH NEXT 2 ROWS ONLY returns the next two — priorities 2 and 3. That makes D the correct answer. A is wrong because it claims pagination happens before duplicate removal. That's backwards — DISTINCT is resolved before OFFSET/FETCH ever executes. B incorrectly suggests the offset skips repeated values in the source data rather than rows in the final deduplicated result. C confuses the source rows with the result rows; FETCH NEXT 2 ROWS ONLY pulls 2 rows from the distinct, sorted result set, not raw source rows — so you'd never see duplicate 2s here. A useful mental model: think of OFFSET/FETCH as a window you slide over the finished result set — whatever SELECT, DISTINCT, and ORDER BY would return on their own — not over raw table data. Always resolve the full logical query first, then paginate.

Start this quiz