Welcome to the third part of our SQL Practice Set in MS SQL Server. Here we'll focus on strengthening our JOINing skills with different tables. Before we continue, here's a brief reminder.

You can query more than one table with JOIN. In the ON clause you specify the joining condition.

SELECT FirstName, LastName, Brand
FROM Person
JOIN Car
  ON Person.Id = Car.OwnerId

This query selects the first and last name of the person, and the brand of the car they own. We use the dot (.) notation to reference the columns of specific tables.

If you'd like to list not only people with a car, but also those who have no car, you can use a LEFT JOIN. It selects ALL rows from the left table (Person), even if there are no corresponding rows in the right table.
Here's an example:

SELECT FirstName, LastName, Brand
FROM Person
LEFT JOIN Car
  ON Person.Id = Car.OwnerId

The result of this query can look like this:

Observe that the column Brand is NULL for people who don't have a car.

We can also rename tables in a JOIN query using aliases, like so:

SELECT C1.Model as Newer, C2.Model as Older
FROM Car C1
JOIN Car C2
  ON C1.Age < C2.Age

Here, we joined the Car table twice to list new and old models together. Note the use of table aliases in the query. Observe that the JOIN condition is a bit non-standard: it's a comparison instead of an equality. Such JOINs are called non-equi JOINs.