Get to know the data
FULL JOIN
OUTER JOINS
Instruction
Okay, we'll now learn a new kind of JOIN: LEFT JOIN.
LEFT JOIN returns all rows from the left table (the first table in the query) plus all matching rows from the right table (the second table in the query).
Let's see an example:
SELECT * FROM Car LEFT JOIN Person ON Car.OwnerID = Person.ID;
The result may look like this:
| Car | Person | |||
|---|---|---|---|---|
| Id | Brand | OwnerId | Id | Name |
| 1 | Ford | 3 | 3 | Jane Preston |
| 2 | Opel | 1 | 1 | Cody Wright |
| 3 | Volkswagen | 2 | 2 | Oscar Walls |
| 4 | Toyota | 4 | 4 | Megan Black |
| 5 | Citroen | NULL | ||
| 6 | Nissan | NULL | ||
| 7 | Skoda | NULL | ||
The LEFT JOIN returns all rows in the above table. The blue rows are returned by the INNER JOIN. The pink rows are added by the LEFT JOIN: there is no matching owner for the pink row cars but a LEFT JOIN returns them nevertheless.
Exercise
Show all information for all students. If a student is assigned to a room, show all information for the room as well.
Stuck? Here's a hint!
Type:
SELECT * FROM Student LEFT JOIN Room ON Student.RoomID = Room.ID;
