SQL Output Based Interview Questions

Q1: GROUP BY with NULL values

id | customer_id
---+------------
1  | 101
2  | NULL
3  | NULL
4  | 102

Query: SELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id;

Even though NULL = NULL is unknown (see NULL Handling in SQL), GROUP BY uses a different rule — it treats every NULL as belonging to the same one group, so both NULL rows collapse together into a single row with count 2, not two separate rows and not zero rows.

Q2: LEFT JOIN with a WHERE clause trap

customers          orders
id | name          id | customer_id | status
1  | Alice         1  | 1            | shipped
2  | Bob           (Bob has no orders)

Query:

SELECT c.name, o.status
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'shipped';

Bob disappears entirely. The LEFT JOIN does preserve him with NULL in o.status — but the WHERE o.status = 'shipped' condition then evaluates NULL = 'shipped', which is NULL, not true, and gets filtered out just like any row that fails WHERE. The fix is moving the condition into the ON clause, or filtering for NULL explicitly if that's the intent. This exact trap is why INNER JOIN vs LEFT JOIN vs RIGHT JOIN is worth knowing cold.

Q3: DISTINCT across multiple columns

email          | source
a@b.com        | ads
a@b.com        | organic
c@d.com        | ads

Query: SELECT DISTINCT email, source FROM signups;

All three rows survive. DISTINCT considers the entire combination of listed columns, and no two rows share the same (email, source) pair — a@b.com repeating in the email column alone doesn't make a row a duplicate.

Q4: CASE WHEN meets NULL

SELECT id,
  CASE
    WHEN discount_code = 'NONE' THEN 'no discount'
    ELSE 'has discount'
  END AS discount_label
FROM orders;
-- discount_code is NULL for order id 7, not the string 'NONE'

discount_code = 'NONE' evaluates to NULL when discount_code is NULL, not true — and CASE falls through to ELSE for anything that isn't exactly true. A dedicated WHEN discount_code IS NULL THEN ... branch is required to catch it explicitly.

Q5: ORDER BY a column not in SELECT

Query: SELECT name FROM employees ORDER BY hire_date DESC;

This runs fine and sorts correctly by hire_date, even though hire_date never appears in the output. In a plain query without DISTINCT or GROUP BY, ORDER BY has access to every column in the underlying table, not just the ones in SELECT — because logically, sorting happens after the row set is fully known, using the full row. See SQL Alias Scope Explained for how this generalizes to aliases too. Add DISTINCT to the same query, though, and most databases will reject an ORDER BY column that isn't part of the selected output.

Q6: WHERE vs HAVING on the same condition

-- Version A
SELECT customer_id, COUNT(*) AS n
FROM orders WHERE status = 'completed'
GROUP BY customer_id;

-- Version B
SELECT customer_id, COUNT(*) AS n
FROM orders
GROUP BY customer_id
HAVING status = 'completed';

Version A runs and filters rows before counting. Version B fails in most databases with an error, because status is neither a grouped column nor wrapped in an aggregate by the time HAVING runs — the two are not interchangeable, and the failure mode is a hard error, not a silently different count. The reasoning is covered fully in Why HAVING Is Used After GROUP BY.