Get to know the data
JOIN revisited
LEFT JOIN
RIGHT JOIN
FULL JOIN
OUTER JOINS
16. The keyword OUTER
NATURAL JOIN
Aliases for tables

Instruction

OK. Remember when we told you that JOIN is short for INNER JOIN?

The three joins we mentioned just now: LEFT JOIN, RIGHT JOIN and FULL JOIN are also shortcuts of some kind. They are all actually OUTER JOINS: LEFT OUTER JOIN, RIGHT OUTER JOIN and FULL OUTER JOIN. You can add the keyword OUTER and the results of your queries will stay the same.

For example, for the LEFT JOIN, you could just as well write:

SELECT
  * 
FROM person 
LEFT OUTER JOIN car 
  ON person.id = car.owner_id;

Exercise

Check it our for yourself. Use the full name RIGHT OUTER JOIN to show all the kettles together with their rooms (even if there is no room assigned).

Stuck? Here's a hint!

Type:

SELECT
  * 
FROM room 
RIGHT OUTER JOIN equipment 
  ON room.id = equipment.room_id 
WHERE equipment.name = 'kettle';