Get to know the data
JOIN revisited
LEFT JOIN
11. LEFT JOIN explained
RIGHT JOIN
FULL JOIN
OUTER JOINS
Aliases for tables

## 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 null null
6 Nissan null null null
7 Skoda null null null

LEFT JOIN returns all rows in the above table. The red rows are the rows returned by INNER JOIN. The green rows are the rows added by a LEFT JOIN: there is no matching owner for the green cars, but a LEFT JOIN returns them nevertheless. The blue column is the one on which we are joining the tables.

## 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;