In the previous step, you used an
INNER JOIN; other joins include
The inner join returns only rows where there is a matching row in both tables, for example:
- A model will only be returned if it has at least one sale
- A sale will only be returned if it has a matching model
Or, put another way:
- If there is a model that has never been sold, it won’t be returned
- If there is a sale without a model, it won’t be returned
INNER JOIN can be thought of as the intersection between two tables.
You previously used an
INNER JOIN to a get a list of computers that had been sold and the date they were sold.
If you also wanted to see the computers that had never been sold, you would need to use a
LEFT JOIN, often referred to as a
LEFT OUTER JOIN.
Run the same query you used previously to get a list of sold computers, changing
SELECT model.name, sales.sale_date FROM model LEFT JOIN sales on model.model_id = sales.model_id;
You will now see that all the computer models are returned by the query, and those computers that have never been sold have a
A left outer join returns all the records from the left (
model) table and those that match in the right (
sales) table. Note that where there are no matching records in the right (
sales) table, a NULL value is returned.
Many other databases also support
RIGHT OUTER JOIN and
FULL OUTER JOIN, although SQLite does not.
RIGHT OUTER JOIN, often referred to as a
RIGHT JOIN, is the opposite of a left outer; it returns all the records from the right table and those that match in the left table.
FULL OUTER JOIN returns all the records from both tables, and where a record cannot be matched, a NULL value is returned.
Joining multiple tables
A single query can include multiple joins to bring in different tables.
If you wanted to include details about the manufacturer in the sales data, you could include a second join to include the
In this example, an inner join is used to join the
manufacturer table so that the
url field can also be returned:
SELECT model.name, sales.sale_date, manufacturer.url FROM model LEFT JOIN sales on model.model_id = sales.model_id INNER JOIN manufacturer on model.manufacturer_id = manufacturer.manufacturer_id;
LEFT joins to create queries that pull in data from multiple tables. Share your experiments in the comments section.