Joins

In the previous step, you used an INNER JOIN; other joins include LEFT, RIGHT, and FULL OUTER.

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

An INNER JOIN can be thought of as the intersection between two tables.

Outer joins

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 INNER to LEFT:

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 sold_date of NULL.

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.

A 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.

A 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 manufacturer table.

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;

Experiment using INNER and LEFT joins to create queries that pull in data from multiple tables. Share your experiments in the comments section.