Set Operations: UNION, INTERSECT & EXCEPT (MINUS)

Algebra over result sets

The operators in this lesson combine two *result sets* (not two tables) into one. They answer "in both", "in either", or "in one but not the other":

- UNION — rows in *either* query; duplicates removed (like DISTINCT). UNION ALL keeps duplicates and is faster. - INTERSECT — rows in *both* queries. - EXCEPT — rows in the first query but not the second. (The Oracle/textbook name is MINUS; Postgres uses EXCEPT — same operation.)

Union compatibility

For any of the three, the two SELECTs must be union-compatible: the same number of columns, in the same order, with compatible data types in each position. The column *names* don't have to match — the result takes its names from the first query. A single trailing ORDER BY applies to the combined result and goes only after the last SELECT.

Worked example: students in both COMP 268 and COMP 378 (INTERSECT)

*Question:* which students have registered in both COMP 268 and COMP 378? Build each enrolled-student set, then intersect:

SELECT r.student_id
FROM   registrations r
JOIN   sections s ON s.section_id = r.section_id
JOIN   courses  c ON c.course_id = s.course_id
WHERE  c.code = 'COMP 268'
INTERSECT
SELECT r.student_id
FROM   registrations r
JOIN   sections s ON s.section_id = r.section_id
JOIN   courses  c ON c.course_id = s.course_id
WHERE  c.code = 'COMP 378';

The first SELECT lists every student enrolled in a COMP 268 section; the second lists every student enrolled in a COMP 378 section; INTERSECT keeps the ids appearing in both. Result: students 1, 4, 9. Both sides project a single student_id column, so they are union-compatible.

Worked example: offered in I-2018 but not II-2018 (EXCEPT)

*Question:* which courses ran in semester I-2018 but not in II-2018?

SELECT course_id FROM sections WHERE semester = 'I-2018'
EXCEPT
SELECT course_id FROM sections WHERE semester = 'II-2018';

EXCEPT keeps course ids in the first set that are absent from the second — courses offered in I-2018 only. Result: courses 1 and 5. Swap the two SELECTs and you get the opposite difference (offered in II-2018 but not I-2018) — order matters for EXCEPT, unlike UNION/INTERSECT.