UNION returns the rows of either query. Two more operators compare the two results:
A: SELECT dept_name FROM student
| dept_name |
|---|
| Comp. Sci. |
| Math |
| Biology |
B: SELECT dept_name FROM instructor
| dept_name |
|---|
| Comp. Sci. |
| Math |
| Music |
| Query | Rows |
|---|---|
A INTERSECT B | Comp. Sci., Math: in both |
A EXCEPT B | Biology: in A, not in B |
B EXCEPT A | Music: in B, not in A |
sql
SELECT dept_name FROM student
EXCEPT
SELECT dept_name FROM instructor;With the full tables, the result is Biology: it has students but no instructors.
- The rules of
UNIONapply: the same number of columns, compatible types, names from the first query. - Both remove duplicate rows.
INTERSECT ALLandEXCEPT ALLkeep them. - For
EXCEPT, the order of the two queries matters. - They compare whole rows, and two NULLs count as the same value here.