Want to keep learning?

This content is taken from the Raspberry Pi Foundation & National Centre for Computing Education's online course, Introduction to Databases and SQL. Join the course to learn more.

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.

a venn diagram of 2 overlapping circles, labelled Table A and Table B, the intersection between the circles is shaded

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.

db browser showing the result of the above query, 21 rows returned, all models of computer are returned, 2 models of computer are returned with a data in the sale_date field and the other records have NULL for sale_date

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.

a venn diagram of 2 overlapping circles, labelled Table A and Table B, circle Table A and the intersection between the circles is shaded

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 venn diagram of 2 overlapping circles, labelled Table A and Table B, circle Table B and the intersection between the circles is shaded

A FULL OUTER JOIN returns all the records from both tables, and where a record cannot be matched, a NULL value is returned.

a venn diagram of 2 overlapping circles, labelled Table A and Table B, circle Table A, circle Table B and the intersection between the circles are shaded

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.

Share this article:

This article is from the free online course:

Introduction to Databases and SQL

Raspberry Pi Foundation