UNION vs UNION ALL in SQL Server, Plus Your First Subquery

JOINs combine tables side by side, adding columns. UNION combines result sets top to bottom, stacking rows — a fundamentally different kind of combination, useful whenever two separate queries produce compatible-shaped rows you want as one result. Subqueries, the second half of this lesson, let a query’s WHERE clause be driven by the result of an entirely separate query.

UNION Combines and Deduplicates

SELECT full_name, 'Austin driver' AS note FROM dbo.Driver WHERE city = 'Austin'
UNION ALL
SELECT full_name, 'High earner' AS note FROM dbo.Driver
WHERE driver_id IN (SELECT driver_id FROM dbo.Trip WHERE fare_usd > 30);

Every SELECT in a UNION must return the same number of columns, in compatible types, in the same order — the column names in the final result come from the first SELECT only. This is worth testing directly:

-- FAILS: mismatched column counts
SELECT full_name FROM dbo.Driver
UNION ALL
SELECT full_name, city FROM dbo.Driver;
-- Msg 205: All queries combined using a UNION, INTERSECT or EXCEPT operator must have
-- an equal number of expressions in their target lists.

UNION vs UNION ALL

UNION runs an implicit dedup step (conceptually similar to SELECT DISTINCT applied to the combined result) — real work that costs real time on large result sets. If you know there’s no overlap between the two queries (as in the example above, since a driver can’t simultaneously fail and pass the same filter), or duplicates are genuinely fine for your use case, UNION ALL is the better default. Reach for plain UNION only when you specifically need duplicates removed.

Two More Set Operators, Briefly

-- INTERSECT: only rows present in BOTH result sets
SELECT city FROM dbo.Driver INTERSECT SELECT city FROM dbo.Driver WHERE driver_id > 2;

-- EXCEPT: rows in the first result set but NOT the second
SELECT city FROM dbo.Driver EXCEPT SELECT city FROM dbo.Driver WHERE driver_id > 2;

Same column-matching rules as UNION apply. These are less common day-to-day than UNION ALL, but genuinely useful for comparison/reconciliation queries — “what’s in this dataset that isn’t in that one.”

Your First Subquery

SELECT full_name
FROM dbo.Driver
WHERE driver_id IN (
    SELECT driver_id FROM dbo.Trip WHERE distance_km > 20
);

The inner SELECT driver_id FROM dbo.Trip WHERE distance_km > 20 runs first (conceptually), producing a list of IDs the outer query then filters against. This pattern — nesting a query inside another’s WHERE clause — is one you’ll use constantly, and it comes in a few distinct shapes:

-- Scalar subquery: returns exactly one value, usable anywhere a single value fits
SELECT full_name FROM dbo.Driver
WHERE driver_id = (SELECT TOP 1 driver_id FROM dbo.Trip ORDER BY fare_usd DESC);

-- Correlated subquery: references the OUTER query's row, re-evaluated per row
SELECT full_name FROM dbo.Driver d
WHERE EXISTS (SELECT 1 FROM dbo.Trip t WHERE t.driver_id = d.driver_id AND t.fare_usd > 25);

That last one — a correlated subquery using EXISTS — is worth flagging early even though it looks more advanced: it’s generally the safer, often faster alternative to IN for “does at least one matching row exist” checks, and unlike NOT IN (Chapter 3), NOT EXISTS handles NULLs correctly with no surprise gotcha.