SQL Fundamentals: Joins, NULL, Aggregate Functions, and Subqueries

Part 14 of 18 · ~2 min

Set Operations: UNION, INTERSECT, EXCEPT

Slide four is hiring gaps — which departments have budget and headcount approved but nobody hired yet. A join answers questions by lining rows up next to each other on shared key values. A set operation does something structurally different: it takes two entire query results and combines or compares them as whole sets, provided both queries hand back the same number of columns in compatible types — there's nothing to match row-to-row here, only sets to union, intersect, or subtract from one another.

-- UNION: the combined, deduplicated set of dept values from both tables at once
SELECT dept FROM Departments
UNION
SELECT dept FROM Employees;
-- Finance, HR, IT, Sales — swap in UNION ALL to keep duplicates and skip paying for the dedup pass

-- INTERSECT: only the dept values that show up on both sides
SELECT dept FROM Departments
INTERSECT
SELECT dept FROM Employees;
-- HR, IT, Sales — a department only survives INTERSECT if it's staffed. Finance drops out here too.

-- EXCEPT (Oracle's dialect calls it MINUS): everything from the first query, minus whatever the second one also has
SELECT dept FROM Departments
EXCEPT
SELECT dept FROM Employees;
-- Finance, alone — the one department Departments has that Employees has never heard of

That last query is the entire hiring-gaps slide, in three lines, with no join anywhere in it: "everything in Departments that doesn't also appear among Employees.dept values" is precisely "departments with zero people." It's a genuinely clean substitute for the LEFT JOIN ... WHERE ... IS NULL pattern from the very first chapter, and it's worth keeping around specifically because "which of these two lists differ" generalizes well beyond headcount. That said, the LEFT JOIN version is still what shows up more often in day-to-day code, for a practical reason: the moment the report needs to pull back actual columns from both tables side by side, rather than just the bare department name, a set operation can't do that and a join can.

One syntax trap worth knowing before you try to sort a set operation's output: ORDER BY is only allowed once, at the very end of the whole statement, and it sorts the final combined result — not either individual SELECT. Attach it to the first branch instead and the engine won't guess what you meant; it just refuses to run:

SELECT dept FROM Departments ORDER BY dept
UNION
SELECT dept FROM Employees;
-- error: ORDER BY clause should come after UNION not before

SELECT dept FROM Departments
UNION
SELECT dept FROM Employees
ORDER BY dept;
-- Finance, HR, IT, Sales — this is the only place ORDER BY is allowed to go

It's a small thing, but it's exactly the kind of small thing that costs you five confused minutes the first time you hit it, because the error message doesn't tell you why — only that the clause is in the wrong place.