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

Instruction

Okay. 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 shorthand. They are all actually OUTER JOINs: LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN. You can add the OUTER keyword; 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.OwnerID;

Exercise

Check it out for yourself. Use the full name RIGHT OUTER JOIN to show all 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.RoomID
WHERE Equipment.Name = N'kettle';