SQL Subqueries, UNION, INTERSECT, EXCEPT and CASE for Reporting

A clinic manager asks which registered patients have never visited. You write WHERE patient_id NOT IN (SELECT patient_id FROM visits), which reads exactly like the question, and the database returns zero rows. There is one such patient, Pavel. Nothing is broken and no error appears; the query is simply answering a different question than the one you asked, because of a single NULL in the visits table. This lesson starts with that surprise and then builds the reporting toolkit around it.

Subqueries and the NOT IN trap

A subquery is a SELECT nested inside another statement. IN (subquery) tests membership; EXISTS (subquery) asks whether the inner query returns at least one row. The data for this lesson is a small clinic: patients, and their visits. Some visits belong to unregistered walk-ins, so visits.patient_id can be NULL.

CREATE TABLE patients (
  patient_id INTEGER PRIMARY KEY,
  name       TEXT NOT NULL,
  plan       TEXT NOT NULL
);

CREATE TABLE visits (
  visit_id   INTEGER PRIMARY KEY,
  patient_id INTEGER REFERENCES patients(patient_id),  -- NULL for unregistered walk-ins
  clinic     TEXT    NOT NULL,
  visit_date TEXT    NOT NULL,
  wait_min   INTEGER NOT NULL
);

INSERT INTO patients VALUES
  (1, 'Mara Ellis',  'basic'), (2, 'Joss Park',   'plus'),
  (3, 'Ines Duarte', 'basic'), (4, 'Tomas Wirth', 'plus'),
  (5, 'Lena Okafor', 'basic'), (6, 'Pavel Novak', 'basic');

INSERT INTO visits (patient_id, clinic, visit_date, wait_min) VALUES
  (1,    'North', '2025-01-08', 12),
  (2,    'North', '2025-01-09', 35),
  (3,    'South', '2025-01-15', 20),
  (1,    'South', '2025-01-22', 48),
  (NULL, 'North', '2025-01-27',  9),
  (5,    'North', '2025-01-29', 22),
  (2,    'South', '2025-02-03', 14),
  (3,    'North', '2025-02-05', 52),
  (4,    'North', '2025-02-10', 17),
  (1,    'North', '2025-02-12',  8),
  (NULL, 'South', '2025-02-18', 25),
  (3,    'South', '2025-02-20', 31);

-- Who has never visited? (looks right, is wrong)
SELECT name
FROM patients
WHERE patient_id NOT IN (SELECT patient_id FROM visits);

-- NOT EXISTS gets it right
SELECT name
FROM patients AS p
WHERE NOT EXISTS (SELECT 1 FROM visits AS v WHERE v.patient_id = p.patient_id);

-- NOT IN works if you remove NULLs from the list yourself
SELECT name
FROM patients
WHERE patient_id NOT IN (SELECT patient_id FROM visits WHERE patient_id IS NOT NULL);

name
----

name
-----------
Pavel Novak

name
-----------
Pavel Novak

The first query printed nothing. x NOT IN (1, 2, NULL) expands to x <> 1 AND x <> 2 AND x <> NULL, and the last comparison is neither true nor false but unknown, so the whole condition can never be true. A single NULL in the list empties the result. The second query, NOT EXISTS, asks "is there any matching visit row?" and is immune, because it compares with = on a correlated row rather than testing membership in a list. The third works only because the NULLs were removed by hand. Prefer NOT EXISTS for "has no related rows", and treat any NOT IN against a nullable column as a bug waiting for data.

Scalar and correlated subqueries

A subquery that returns exactly one row and one column can be used wherever a value can: a scalar subquery. A correlated subquery refers to a column of the outer row, so it conceptually runs once per outer row.

SELECT visit_id, clinic, wait_min
FROM visits
WHERE wait_min > (SELECT AVG(wait_min) FROM visits)
ORDER BY wait_min DESC;

SELECT p.name,
       (SELECT COUNT(*)        FROM visits AS v WHERE v.patient_id = p.patient_id) AS visits,
       (SELECT MAX(visit_date) FROM visits AS v WHERE v.patient_id = p.patient_id) AS last_visit
FROM patients AS p
ORDER BY p.patient_id;

visit_id | clinic | wait_min
---------+--------+---------
       8 | North  |       52
       4 | South  |       48
       2 | North  |       35
      12 | South  |       31
      11 | South  |       25

name        | visits | last_visit
------------+--------+-----------
Mara Ellis  |      3 | 2025-02-12
Joss Park   |      2 | 2025-02-03
Ines Duarte |      3 | 2025-02-20
Tomas Wirth |      1 | 2025-02-10
Lena Okafor |      1 | 2025-01-29
Pavel Novak |      0 | NULL

The first query keeps visits that waited longer than the overall average (about 24.4 minutes); the subquery runs once because it does not depend on the outer row. The second query computes, for each patient, a count and a latest date, with the correlation v.patient_id = p.patient_id. Patients with no visits get 0 and NULL. Correlated subqueries are easy to write and read, but on large tables a join with GROUP BY, or a window function from the previous lesson, is often faster, since the optimiser may not be able to run the correlated form as a single pass. Always compare plans before assuming either way.

Set operations: combining whole result sets

Set operators stack the results of two queries that have the same number of columns and compatible types. UNION keeps each distinct row once, UNION ALL keeps everything, INTERSECT keeps rows present in both, and EXCEPT keeps rows in the first but not the second. Duplicates are compared across all selected columns, and NULLs count as equal to each other for this purpose, unlike in a WHERE comparison.

-- seen in January AND in February
SELECT patient_id FROM visits WHERE visit_date <  '2025-02-01' AND patient_id IS NOT NULL
INTERSECT
SELECT patient_id FROM visits WHERE visit_date >= '2025-02-01' AND patient_id IS NOT NULL
ORDER BY patient_id;

-- seen in January but NOT in February
SELECT patient_id FROM visits WHERE visit_date <  '2025-02-01' AND patient_id IS NOT NULL
EXCEPT
SELECT patient_id FROM visits WHERE visit_date >= '2025-02-01' AND patient_id IS NOT NULL
ORDER BY patient_id;

-- UNION removes duplicates, UNION ALL keeps every row
WITH jan AS (SELECT patient_id FROM visits WHERE visit_date <  '2025-02-01' AND patient_id IS NOT NULL),
     feb AS (SELECT patient_id FROM visits WHERE visit_date >= '2025-02-01' AND patient_id IS NOT NULL)
SELECT (SELECT COUNT(*) FROM (SELECT * FROM jan UNION     SELECT * FROM feb)) AS union_rows,
       (SELECT COUNT(*) FROM (SELECT * FROM jan UNION ALL SELECT * FROM feb)) AS union_all_rows;

patient_id
----------
         1
         2
         3

patient_id
----------
         5

union_rows | union_all_rows
-----------+---------------
         5 |             10

Patients 1, 2 and 3 were seen in both months, patient 5 (Lena) only in January, and the union holds five distinct patients against ten rows with UNION ALL, because several patients visited more than once. A single ORDER BY after the last query sorts the combined result. Dialect notes: Oracle calls EXCEPT by the name MINUS, and MySQL only supports INTERSECT and EXCEPT from version 8.0.31. The same logic can be written with joins or EXISTS, and which reads better depends on the question.

CASE: logic inside a query

CASE is SQL's conditional expression. The searched form evaluates conditions top to bottom and returns the first match, or the ELSE value, or NULL if there is no ELSE. It works in SELECT, WHERE, ORDER BY and, importantly, inside aggregates.

SELECT visit_id, wait_min,
       CASE
         WHEN wait_min <= 15 THEN 'quick'
         WHEN wait_min <= 30 THEN 'acceptable'
         ELSE 'long'
       END AS wait_band
FROM visits
ORDER BY visit_id;

visit_id | wait_min | wait_band
---------+----------+-----------
       1 |       12 | quick
       2 |       35 | long
       3 |       20 | acceptable
       4 |       48 | long
       5 |        9 | quick
       6 |       22 | acceptable
       7 |       14 | quick
       8 |       52 | long
       9 |       17 | acceptable
      10 |        8 | quick
      11 |       25 | acceptable
      12 |       31 | long

Order matters: a wait of 12 satisfies both <= 15 and <= 30, and gets 'quick' because that branch comes first. Banding values like this turns raw numbers into report categories. The same expression inside SUM gives conditional aggregation, the standard way to produce a pivot-style table in a single pass over the data.

SELECT clinic,
       COUNT(*) AS visits,
       SUM(CASE WHEN visit_date <  '2025-02-01' THEN 1 ELSE 0 END) AS jan,
       SUM(CASE WHEN visit_date >= '2025-02-01' THEN 1 ELSE 0 END) AS feb,
       ROUND(100.0 * SUM(CASE WHEN wait_min > 30 THEN 1 ELSE 0 END) / COUNT(*), 0) AS pct_long_waits
FROM visits
GROUP BY clinic;

clinic | visits | jan | feb | pct_long_waits
-------+--------+-----+-----+---------------
North  |      7 |   4 |   3 |           29.0
South  |      5 |   2 |   3 |           40.0

Each clinic row now carries its January and February counts as separate columns and the percentage of waits over 30 minutes, without a join or a second query. The pattern is SUM(CASE WHEN condition THEN 1 ELSE 0 END) for counts, or AVG(CASE WHEN ... THEN value END) for conditional averages, where the missing ELSE deliberately produces NULL so the row is skipped.

Common mistakes

• NOT IN against a column that can be NULL. Symptom: an empty result for a "never happened" report. Use NOT EXISTS, or filter the NULLs out of the subquery.
• Mismatched columns in a set operation. Error: SELECTs to the left and right of INTERSECT do not have the same number of result columns (SQLite wording). Align the column lists and types.
• UNION when you meant UNION ALL. Symptom: totals that are too low, plus an expensive sort to find duplicates. Use UNION ALL unless you really want deduplication.
• A scalar subquery that returns several rows. PostgreSQL raises more than one row returned by a subquery used as an expression; SQLite quietly uses the first. Make the subquery return one row with an aggregate or LIMIT 1 plus an ORDER BY.
• CASE branches in the wrong order. Symptom: rows landing in the first band that matches rather than the best one. Put the most specific condition first.

How this shows up in real projects and interviews

Reporting queries are mostly these four tools: "customers with no orders" (NOT EXISTS), "above average" (scalar subquery), "in both lists" (INTERSECT or a join) and "counts per category per month" (CASE inside aggregates). Interviewers like the NOT IN with NULL question because it separates people who have debugged real data from people who know only syntax. Once reports become complex, name the intermediate steps with CTEs, from the CTE lesson, to keep them reviewable.