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.