Skip main navigation

Joins

Description of SQL INNER, LEFT and RIGHT 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.

This article is from the free online

Introduction to Databases and SQL

Created by
FutureLearn - Learning For Life

Our purpose is to transform access to education.

We offer a diverse selection of courses from leading universities and cultural institutions from around the world. These are delivered one step at a time, and are accessible on mobile, tablet and desktop, so you can fit learning around your life.

We believe learning should be an enjoyable, social experience, so our courses offer the opportunity to discuss what you’re learning with others as you go, helping you make fresh discoveries and form new ideas.
You can unlock new opportunities with unlimited access to hundreds of online short courses for a year by subscribing to our Unlimited package. Build your knowledge with top universities and organisations.

Learn more about how FutureLearn is transforming access to education