Summer Deals - hours only!Up to 80% off on all courses and bundles.-Close
Get to know the data
JOIN revisited
LEFT JOIN
11. LEFT JOIN explained
RIGHT JOIN
FULL JOIN
OUTER JOINS
NATURAL JOIN
Aliases for tables

Instruction

OK, we'll now learn a new kind of JOIN: LEFT JOIN.

LEFT JOIN works in the following way: it 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.owner_id = person.id;

The result may look like this:

car person
id brand owner_id 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 rows from the table student. If a student is assigned to a room, show the room data as well.

Stuck? Here's a hint!

Type:

SELECT *
FROM student
LEFT JOIN room
  ON student.room_id = room.id;