UNION, INTERSECT and EXCEPT

Part of the free LogicWiz SQL & Databases course, Module 6: Subqueries and set operations. The queries and practice run in the app on real Postgres.

The placement cell is mailing two groups: final-years, and anyone with a CGPA under 6.5 who needs a mentor. If a final-year student also has a low CGPA, should they get two emails?

You'll learn to

  • Stack two results with UNION and UNION ALL
  • Find common rows with INTERSECT and differences with EXCEPT
  • Follow the rules: same column count, compatible types, one ORDER BY
  • Pick UNION ALL when duplicates are real data

Learn

Joins go sideways, set operations stack

A join puts columns side by side. A set operation puts two result tables one under the other.

  • UNION: rows in either result, duplicates removed
  • UNION ALL: rows in either result, duplicates kept
  • INTERSECT: rows in both
  • EXCEPT: rows in the first but not the second

Both queries must return the same number of columns with compatible types. The column names come from the first query.

UNION: one email each

SELECT name, email FROM students WHERE year = 4
UNION
SELECT name, email FROM students WHERE cgpa < 6.5
ORDER BY name;

Final-years plus students under 6.5. No duplicates here, but if someone were in both groups they would appear once. The single ORDER BY at the end sorts the combined result.

UNION ALL keeps every row

SELECT city FROM students WHERE dept_id = 1
UNION ALL
SELECT city FROM students WHERE dept_id = 2
ORDER BY city;

Delhi and Hyderabad appear twice, once from each half, and Mumbai twice from the CS half alone. UNION ALL does no duplicate check, so it is also faster. Use it when duplicates are real (two payments of ₹60 are two payments) or impossible.

INTERSECT and EXCEPT

-- took BOTH Programming in C and Engineering Mathematics
SELECT student_id FROM enrollments WHERE course_id = 'CS101'
INTERSECT
SELECT student_id FROM enrollments WHERE course_id = 'MA101'
ORDER BY student_id;

-- took Engineering Mathematics but NOT Programming in C
SELECT student_id FROM enrollments WHERE course_id = 'MA101'
EXCEPT
SELECT student_id FROM enrollments WHERE course_id = 'CS101'
ORDER BY student_id;

INTERSECT gives 101, 102 and 107. EXCEPT is order-sensitive: A EXCEPT B is not B EXCEPT A.

Three rules that cause most errors:

  • Same number of columns on both sides, in the same order. SELECT name, city … UNION SELECT name … fails.
  • Compatible types: a text column can't stack on an integer one.
  • One ORDER BY, at the very end. An ORDER BY after the first query is a syntax error.

EXCEPT has no NULL trap

EXCEPT compares whole rows and treats two NULLs as the same (like DISTINCT does). So

SELECT dept_id FROM departments EXCEPT SELECT dept_id FROM students

correctly returns 6 (Mathematics), even though Manish's NULL is in the second list. Compare that with NOT IN, which returned nothing. Note that EXCEPT and INTERSECT also remove duplicates; add ALL to keep them.

MySQL supports INTERSECT and EXCEPT only from version 8.0.31; older versions need IN/NOT EXISTS instead. Oracle spells EXCEPT as MINUS. UNION and UNION ALL work everywhere.

"UNION vs UNION ALL?" is asked in nearly every SQL interview. Answer: UNION removes duplicate rows (extra sort or hash work); UNION ALL keeps them and is faster. In exams, these are relational algebra's union (∪), intersection (∩) and difference (−), and both relations must be union-compatible: same number of attributes with matching domains.

In one line

UNION stacks and de-duplicates (UNION ALL keeps duplicates); INTERSECT = in both, EXCEPT = in first only; one ORDER BY at the end.