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.

SQL Server Temp Tables: A First Look Before You Need the Full Picture

Sometimes a problem is genuinely easier to solve in two steps than one giant nested query. Temp tables are SQL Server’s answer to “I need somewhere to put an intermediate result while I keep working.”

A Local Temp Table in Action

CREATE TABLE #HighValueTrips (
    trip_id INT,
    fare_usd DECIMAL(8,2)
);

INSERT INTO #HighValueTrips
SELECT trip_id, fare_usd FROM dbo.Trip WHERE fare_usd > 20;

SELECT * FROM #HighValueTrips;

DROP TABLE #HighValueTrips;

Notice this is genuinely a real table — it has its own CREATE TABLE, accepts INSERT, and can be queried, filtered, and even joined to other tables exactly like a permanent one, for as long as your session lasts.

What Makes It “Temporary”

A local temp table (prefixed with #) physically lives in the special system database tempdb, not your regular database — but is visible only to the session that created it, and is automatically cleaned up when that session ends, or you can drop it explicitly as shown above. Two sessions can both create a table named #Scratch at the same time without any conflict; SQL Server keeps them completely separate internally.

A Realistic Two-Step Use Case

-- Step 1: capture an expensive-to-compute intermediate result once
SELECT driver_id, COUNT(*) AS trip_count, SUM(fare_usd) AS total_earned
INTO #DriverSummary
FROM dbo.Trip
GROUP BY driver_id;

-- Step 2: reuse it multiple times without recomputing the aggregation
SELECT * FROM #DriverSummary WHERE trip_count > 5;
SELECT d.full_name, s.total_earned
FROM dbo.Driver d JOIN #DriverSummary s ON d.driver_id = s.driver_id
ORDER BY s.total_earned DESC;

SELECT ... INTO creates the temp table and populates it in one statement, inferring column types automatically — a common shortcut once you’re comfortable with the explicit CREATE TABLE form shown earlier.

Why Not Just Use a Bigger Subquery?

You often could. The tradeoff: a temp table computes its result once and lets you reuse and re-query it as many times as needed; a subquery or CTE re-runs its logic each time it’s referenced (with some caveats the advanced course covers). For a genuinely expensive intermediate calculation you need to reuse several times in a longer script, a temp table can be both clearer to read and faster to run.

Just the Beginning

This is a preview — the full comparison of local temp tables, table variables, and global temp tables (prefixed ##, visible across all sessions), including exactly when each one is the right tool for a given job and their real performance differences, is a dedicated chapter (Chapter 3) in SQL Server for Developers & DBAs. For now, know that temp tables exist and behave like session-scoped scratch space you can CREATE, INSERT into, query, and DROP just like any other table.