OUTER JOIN

How LEFT, RIGHT and FULL outer joins keep unmatched rows from one or both tables, and how to get a full join in MySQL, which has no FULL OUTER JOIN.

Journeyman · SQL · MySQL · Updated

By

With outer joins, you have the ability to return all of the rows from a table, even if there are no related rows in the table being joined.

When joining two tables, the first table in the query is referred to as the LEFT table and the second is referred to as the RIGHT table.

SELECT *
FROM person LEFT OUTER JOIN task
    ON person.person_id = task.person_id;

In this example, the person table is the LEFT table because it was listed first: it is to the left of the task table in the query.

`LEFT OUTER JOIN`

A LEFT OUTER JOIN returns all of the rows from the LEFT table, even if there are no related rows in the RIGHT table.

SELECT *
FROM person
LEFT OUTER JOIN task
    ON person.person_id = task.person_id

LEFT JOIN returns all people even if they are not assigned a task

Notice that the result of this query includes Katrina, but mopping and change oil are still omitted.

Katrina is included in the results of the LEFT JOIN

`RIGHT OUTER JOIN`

A RIGHT OUTER JOIN returns all the rows from the RIGHT table, even if there are no related rows in the LEFT table.

SELECT *
FROM person
RIGHT OUTER JOIN task
    ON person.person_id = task.person_id

RIGHT JOIN returns all tasks even if they are not assigned to a person

Notice that the result of this query includes mopping and change oil, but Katrina is omitted.

mopping and change oil are included in the results of the RIGHT JOIN

`FULL OUTER JOIN`

A FULL OUTER JOIN returns all of the rows from both tables, even when a row has no match in the other table. Many databases write it like this (MySQL doesn't; see below):

-- not in MySQL: this is the syntax SQL Server and PostgreSQL use
SELECT *
FROM person
FULL OUTER JOIN task
    ON person.person_id = task.person_id

MySQL does not support the FULL OUTER JOIN syntax, but you can get the same result: run a LEFT JOIN and a RIGHT JOIN, then merge them with UNION, which drops the rows that appear in both.

SELECT *
FROM person
LEFT OUTER JOIN task
    ON person.person_id = task.person_id

UNION

SELECT *
FROM person
RIGHT OUTER JOIN task
    ON person.person_id = task.person_id;

FULL JOIN returns all people AND all tasks

Notice that the result of this query includes Katrina, as well as mopping and change oil.

All rows from all tables are returned with a FULL JOIN

Do it: find the products nobody has ordered

The store wants to know which products have never been ordered, so it can decide whether to keep them.

Two tables hold what that needs:

  • products: each product's product_id and product_name.
  • order_details: one row per order line, with its order_id, the product_id it sold, and the quantity.

They hold these rows (the Tables tab shows the SQL that makes them):

product_id product_name
1 Chai
2 Chang
3 Aniseed Syrup
4 Chef Anton's Cajun Seasoning
5 Chef Anton's Gumbo Mix
6 Grandma's Boysenberry Spread
order_id product_id quantity
10248 2 10
10249 1 9
10250 4 15
10251 2 6
10252 1 40

The starter's inner join lists the products that have been ordered, one row per order line, so Chai and Chang each come twice. A product that was never ordered has no line to pair with, so the inner join leaves it out. Those are the ones the store wants.

For example, Aniseed Syrup is product 3. No row in order_details has product_id 3, so a left join gives Aniseed Syrup one row with NULL in every order_details column. That NULL is how you know it was never ordered.

Your query should give exactly these rows, in this order:

product_id product_name
3 Aniseed Syrup
5 Chef Anton's Gumbo Mix
6 Grandma's Boysenberry Spread

Your job: change the starter's query so it lists the products that have never been ordered.

  • Show two columns, product_id and product_name, in that order, from products.
  • Keep every product, matched or not, with a LEFT OUTER JOIN from products to order_details.
  • Keep only the rows where no order line matched.
  • Sort the rows by product_name, A to Z.

Practiced live on the lesson page: write the SQL right there and check it.

Cheat sheets