Outer joins
LEFT, RIGHT and FULL OUTER JOIN: keeping rows without a partner by padding them with NULL, finding “those who have none” with IS NULL, and avoiding the trap of a condition put in WHERE.
- 18 cards
- 4 topics
- Free
The outline, topic by topic
Each topic is weighted the way the exam weighs it.
- Concepts
- Questions
- Sort and link
- The numbers
A few cards from the deck
LEFT JOIN: what is it?
Show the answer
A join that keeps every row of the left table, with NULLs on the right when no row matches.
client LEFT JOIN commandereturns every client: those without orders have NULL in all commande columns.Which query returns the clients who placed no order?
FROM client LEFT JOIN commande ON commande.id_client = client.id WHERE commande.id IS NULLFROM client JOIN commande ON commande.id_client = client.id WHERE commande.id IS NULLFROM client LEFT JOIN commande ON commande.id_client = client.id WHERE client.id IS NULLFROM client LEFT JOIN commande ON commande.id_client = client.id WHERE commande.id = NULL
Show the answer
FROM client LEFT JOIN commande ON commande.id_client = client.id WHERE commande.id IS NULLThe inner join keeps only matched clients, so it never finds a NULL. Testing client.id is useless: the client's key is never NULL. And
= NULLis never true.In
client LEFT JOIN commande, clients without orders are kept, with … in the columns of ….Show the answer
In
client LEFT JOIN commande, clients without orders are kept, with NULL in the columns of commande.The left table is entirely preserved; it is the right one that gets padded.
RIGHT JOIN: what is it?
Show the answer
A join that keeps every row of the right table; it is the mirror image of the one that keeps the left.
a RIGHT JOIN breturns the same rows asb LEFT JOIN a. LEFT is often preferred, with the table to keep on the left.