sql · level 3 · unit 1 · lesson 4
- meet it
- guess first
- your turn
- fix it
- nailed it
Tap a green word if you get lost.
meet it
finds rows in both lists. finds leftovers.
Two lists, two questions. Who is in both? INTERSECT keeps only the rows that appear in both results. Who is in the first list but not the second? EXCEPT keeps those.
The rules match UNION: the same number of columns on each side, repeats removed, one ORDER BY at the end. Two rows count as the same only when every column matches.
Order matters for EXCEPT. Chess EXCEPT choir means chess pupils who skip choir. Swap the sides and you get choir pupils who skip chess.
SELECT pupil FROM chess
INTERSECT
SELECT pupil FROM choir
ORDER BY pupil;
SELECT pupil FROM chess
EXCEPT
SELECT pupil FROM choir
ORDER BY pupil;Several of these can chain, and they run top to bottom, one after another.
guess first
Choir comes first this time. Who is left?
Guess before you run it. Wrong guesses are how you learn, and nothing is marked.
SELECT pupil FROM choir
EXCEPT
SELECT pupil FROM chess
ORDER BY pupil;your turn
Your turn. Who could join football?
The coach wants pupils from chess or choir who do not play football yet. Stack chess and choir, then take away football. Fill both blanks.
result
Your answer appears here as a table.
fix it
Nobody is in both clubs? Fix it!
result
Your answer appears here as a table.
- There is no error, but there are no rows. Look at every column each side gives back.
INTERSECTkeeps a row only when every column matches, and 'chess' never equals 'choir'.- Remove the club labels so each side gives only pupil, then press run.
nailed it
you compared lists in a few lines. :D
INTERSECTkeeps rows found in both results.EXCEPTkeeps rows from the first result missing from the second.- Rows match only when every column matches.
new words in your collection
- INTERSECT
- EXCEPT
This lesson isn’t saved. A free account remembers every lesson you finish.