Choosing a JOIN type
- SQL course
- Intermediate
- Lesson 17/115
- 4 min
Pick the right JOIN for each scenario.
Choosing join types
The right JOIN type depends on the question. Pick INNER for matches, LEFT for missing records.
SELECT * FROM a
LEFT JOIN b ON a.id = b.id;All a rows appear.
All columns are selected from a. b ON is joined, keeping every row of the left table.
Choosing join types — details
None is bad; each has its purpose. Row coverage and NULL requirements decide.
Choose INNER for matches only, LEFT to keep one side complete, FULL to keep both sides complete, and CROSS for every combination.
Practice Choosing join types
Use LEFT JOIN to keep all rows of a joined with b.
Starter code
-- pick type
SELECT * FROM aSample solution
SELECT * FROM a
LEFT JOIN b ON a.id = b.id;Expected output
id name 1 x 2 NULL
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 Pick join type
Question
SELECT * FROM a
____ b ON a.id = b.id;
Answer
SELECT * FROM a
LEFT JOIN b ON a.id = b.id;Explanation: LEFT JOIN is the right answer.
2 Choosing a JOIN type: match the terms
| Term | Meaning |
|---|---|
INNER |
intersection |
LEFT |
all left |
RIGHT |
all right |
CROSS |
cartesian |
3 Which JOIN for missing records?
- UNION
- INNER JOIN
- CROSS JOIN
- LEFT JOIN Correct answer
Explanation: LEFT JOIN also brings missing records.