What if we want to combine two tables and keep some of the un-matched rows?
SQL lets us do this through a command called LEFT JOIN
. A left join will keep all rows from the first table, regardless of whether there is a matching row in the second table.
Consider the following animation:
The first and last rows have matching values of c2
. The middle rows do not match. The final result will keep all rows of the first table but will omit the un-matched row from the second table.
This animation represents a table operation produced by the following command:
SELECT * FROM table1 LEFT JOIN table2 ON table1.c2 = table2.c2;
- The first line selects all columns from both tables.
- The second line selects
table1
(the “left” table). - The third line performs a
LEFT JOIN
ontable2
(the “right” table). - The fourth line tells SQL how to perform the join (by looking for matching values in column
c2
).
Instructions
Let’s return to our newspaper
and online
subscribers.
Suppose we want to know how many users subscribe to the print newspaper, but not to the online.
Start by performing a left join of newspaper
table and online
table on their id
columns and selecting all columns.
Don’t remove your previous query.
In order to find which users do not subscribe to the online edition, we need to add a WHERE
clause.
Add a second query after your first one that adds the following WHERE
clause and condition:
WHERE online.id IS NULL
This will select rows where there was no corresponding row from the online
table.