Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

SQL Basics in MS SQL Server

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