UNION / INTERSECT / EXCEPT and their ALL variants

0 · Recall Warm-up

Answer from memory first. Answers are in the Answer Vault at the bottom.

1. After a LEFT JOIN, which COUNT gives 0 for unmatched rows? from Lesson 3.3.6
2. What are the size bounds of R ∪ S in relational algebra? from Lesson 3.1.2
3. Does SQL remove duplicates by default in SELECT? from Lesson 3.3.3

1 · The Hook

SELECT keeps duplicates. But UNION, INTERSECT and EXCEPT, by default, remove them, behaving like relational algebra. Add ALL and they keep duplicates, with exact counting rules. GATE gives two bags and asks how many rows each version returns.

2 · Predict First

A = SELECT roll FROM ENROLL WHERE grade = 'A' gives 1, 1, 3. B = SELECT roll FROM ENROLL WHERE grade = 'B' gives 1, 2, 3. How many rows does A UNION B return? A UNION ALL B?

Make a guess in your head. The answer opens Section 3.

3 · Core Concept

Predict First — the answer

UNION: {1, 2, 3} → 3 rows (duplicates removed). UNION ALL: all 6 values kept → 6 rows.

The set operators

query1 UNION     query2      -- rows in either, duplicates removed
query1 INTERSECT query2      -- rows in both, duplicates removed
query1 EXCEPT    query2      -- rows in query1 not in query2, duplicates removed (Oracle: MINUS)

Both queries must be union compatible: same number of columns, compatible types (Lesson 3.1.2). Column names come from the first query.

ALL versions (bag counting)

If a row appears m times in query1 and n times in query2:

With A = {1, 1, 3} and B = {1, 2, 3}:

Note: the plain versions remove duplicates even when the inputs are bags.

Using them

• "Rolls who got an A or a B" → UNION.
• "Rolls who got both an A and a B" → INTERSECT (1 and 3).
• "Students who took no course" → SELECT roll FROM STUDENT EXCEPT SELECT roll FROM ENROLL → 5 (Esha).

Support varies: MySQL added INTERSECT/EXCEPT only in version 8.0.31; SQLite supports UNION ALL but not INTERSECT ALL or EXCEPT ALL. GATE assumes standard SQL.

NULL in set operators

Set operators compare rows for duplicates, and for that purpose two NULLs are treated as equal (like DISTINCT). So {NULL} INTERSECT {NULL} returns one NULL row, although NULL = NULL is UNKNOWN in WHERE.

4 · Visual

value 1:  A has 2 copies, B has 1 copy
  UNION ALL      2 + 1          = 3 copies
  INTERSECT ALL  min(2, 1)      = 1 copy
  EXCEPT ALL     max(0, 2 - 1)  = 1 copy
  UNION / INTERSECT / EXCEPT    -> 1, 1, 0 copies (set rules)

5 · Worked Examples

Example 1 Easy

SELECT dept FROM STUDENT UNION SELECT dept FROM COURSE; How many rows?

Solution

STUDENT depts {CS, EE, ME}, COURSE depts {CS, EE, ME} → 3.

Example 2 Medium

SELECT dept FROM STUDENT UNION ALL SELECT dept FROM COURSE; How many rows?

Solution

5 + 4 = 9.

Example 3 GATE

R has a value v 5 times; S has v 3 times. How many copies of v in R EXCEPT ALL S, S EXCEPT ALL R, R INTERSECT ALL S, R EXCEPT S?

Solution

max(0, 5 − 3) = 2; max(0, 3 − 5) = 0; min(5, 3) = 3; R EXCEPT S: v is in S, so 0.

8 · Connect the Dots

Lesson 3.3.6 combined tables side by side (joins); this lesson stacked whole results (set operators). Next: putting one query inside another: subqueries with IN, ANY, ALL and EXISTS.

9 · Self-Test

GATE format. Try every question before opening the vault.

Q1 · NAT · 1 mark(s) — UNION ALL

How many rows: SELECT roll FROM ENROLL UNION ALL SELECT roll FROM STUDENT;

Numerical answer: ________

Q2 · NAT · 1 mark(s) — UNION

How many rows: SELECT roll FROM ENROLL UNION SELECT roll FROM STUDENT;

Numerical answer: ________

Q3 · NAT · 2 mark(s) — EXCEPT ALL

R contains the values 4, 4, 4, 7, 7, 9 and S contains 4, 7, 7, 7, 8. How many rows in R EXCEPT ALL S?

Numerical answer: ________

Q4 · NAT · 2 mark(s) — INTERSECT ALL

Same R and S. How many rows in R INTERSECT ALL S?

Numerical answer: ________

Q5 · MCQ · 1 mark(s) — EXCEPT

SELECT cid FROM COURSE EXCEPT SELECT cid FROM ENROLL; returns:

1. (A) C1, C2, C3, C4
2. (B) C4
3. (C) an error
4. (D) no rows

11 · Concept Ledger

Unlocked in this lesson. From here on, these terms may be used.

UNIONINTERSECTEXCEPT (MINUS)UNION ALLINTERSECT ALLEXCEPT ALLbag counting rules