# 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 November 16, 2026

By Gregor Dzierzon

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.

```sql
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.

```sql
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](https://fsdtrainingmedia.blob.core.windows.net/public/images/2026/09/105a501150164245bfdb40ef06d8d26a.png "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](https://fsdtrainingmedia.blob.core.windows.net/public/images/2026/09/be31b0a4382140a9b89c8b68e3549ad8.png "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.

```sql
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](https://fsdtrainingmedia.blob.core.windows.net/public/images/2026/09/dd36f858c656483e9360217e302469a4.png "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](https://fsdtrainingmedia.blob.core.windows.net/public/images/2026/09/60f6f572c4bb406d878395be387377e9.png "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):

```sql
-- 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.

```sql
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](https://fsdtrainingmedia.blob.core.windows.net/public/images/2026/09/4fcbe7bc08634eecaa816c6e2a115b43.png "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](https://fsdtrainingmedia.blob.core.windows.net/public/images/2026/09/6b2c47b0700646b08c6e07a7519dad99.png "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

- [OUTER JOIN](https://www.ask-codey.com/cheat-sheets/joining-tables-outer-join-cheat-sheet)

Source: https://www.ask-codey.com/lessons/joining-tables-outer-join
