Welcome to the third part of our SQL Practice Set. Here we'll focus on strengthening our JOIN
ing 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 first_name, last_name, brand
FROM person
JOIN car
ON person.id = car.owner_id
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 a person doesn't have a car and we want to list them, we can use a LEFT JOIN
. It selects ALL rows from the left table, even if there are no corresponding rows in the right table.
Here's an example:
SELECT first_name, last_name, brand
FROM person
LEFT JOIN car
ON person.id = car.owner_id
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 JOIN
s are called non-equi JOIN
s.