Subqueries & CASE WHEN

Nest queries inside queries and add conditional logic with CASE WHEN

Knowledge0%

Learn & Drill

Fluency0%

Drill & Speed

Retention0%

Mastery & Review

Confidence0%

All modes

Practice

Free tier: read the explanation here. Upgrade to Pro for Drills, Speed challenges & Mastery badges.

Upgrade

Knowledge

Fluency

Retention

Knowledge Debt detected

You can study this freely — but your score may plateau if these foundations have gaps. The Mastery badge requires them to be solid.

GROUP BY & HAVING0%

Explanation

A subquery is a query nested inside another query. It runs first (or per-row), and its result is used by the outer query.

Subquery in WHERE (scalar subquery) — returns a single value: ``sql -- Claims above the average amount SELECT * FROM claims WHERE amount > (SELECT AVG(amount) FROM claims);

Subquery with IN — returns a list of values: ``sql -- Patients who have at least one denied claim SELECT * FROM patients WHERE id IN (SELECT patient_id FROM claims WHERE status = 'denied');

Subquery in FROM (derived table) — must have an alias: ``sql SELECT payer, avg_amount FROM ( SELECT payer, AVG(amount) AS avg_amount FROM claims GROUP BY payer ) AS payer_avgs WHERE avg_amount > 500;

Correlated subquery — references a column from the outer query, so it re-runs for every outer row: ``sql -- Employees earning more than their department's average SELECT e.name, e.salary, e.department FROM employees e WHERE e.salary > ( SELECT AVG(e2.salary) FROM employees e2 WHERE e2.department = e.department -- correlation );

EXISTS / NOT EXISTS — checks whether a correlated subquery returns *any* rows (often faster than IN for large sets): ``sql SELECT * FROM patients p WHERE EXISTS ( SELECT 1 FROM claims c WHERE c.patient_id = p.id AND c.status = 'denied' );

CASE WHEN — inline conditional logic, like an if/elif/else for a single column: ``sql SELECT name, amount, CASE WHEN amount > 1000 THEN 'high' WHEN amount > 100 THEN 'medium' ELSE 'low' END AS amount_tier FROM claims;

CASE WHEN inside an aggregate — the "conditional counting" pattern, used constantly for reports: ``sql -- Denial rate per payer SELECT payer, COUNT(*) AS total, SUM(CASE WHEN status = 'denied' THEN 1 ELSE 0 END) AS denied, ROUND( SUM(CASE WHEN status = 'denied' THEN 1.0 ELSE 0 END) / COUNT(*) * 100, 2 ) AS denial_rate_pct FROM claims GROUP BY payer;

Watch out for NOT IN with NULLs: if the subquery returns even one NULL, NOT IN returns no rows at all (NULL comparisons are unknown, not false). Use NOT EXISTS instead when the subquery column can contain NULLs.

Examples

Denial rate per payer (CASE inside aggregate)

CASE WHEN turns a row-by-row condition into a 1/0 flag, so SUM() can count matches and division can compute a rate.

SELECT payer,
  COUNT(*) AS total,
  SUM(CASE WHEN status = 'denied' THEN 1 ELSE 0 END) AS denied,
  ROUND(
    SUM(CASE WHEN status = 'denied' THEN 1.0 ELSE 0 END) / COUNT(*) * 100, 2
  ) AS denial_rate_pct
FROM claims
GROUP BY payer
ORDER BY denial_rate_pct DESC;

Correlated subquery: above department average

The inner query re-runs for each outer row, comparing each employee only to their own department's average.

SELECT e.name, e.salary, e.department
FROM employees e
WHERE e.salary > (
  SELECT AVG(e2.salary)
  FROM employees e2
  WHERE e2.department = e.department
)
ORDER BY e.department, e.salary DESC;

How well did you understand this?

Next in SQL & Databases

CREATE TABLE, UPDATE & DELETE

Continue

Unlocks

Window Functions