# OUTER JOIN

LEFT JOIN keeps every row of the first table, matched or not; RIGHT JOIN keeps every row of the second; FULL OUTER JOIN keeps both. Rows with no match get NULL, which also lets you find what's missing.

Journeyman · T-SQL · SQL · Updated November 15, 2026

By Gregor Dzierzon

## After this lesson you can

- Keep rows without a match with LEFT, RIGHT and FULL OUTER JOIN
- Find rows that have no match with a LEFT JOIN and IS NULL

## Key takeaways

- `LEFT JOIN` keeps every row of the table named first, `RIGHT JOIN` every row of the joined table, and `FULL OUTER JOIN` every row of both.
- A row with no match gets `NULL` in every column of the other table.
- To find rows with no match, use a `LEFT JOIN`, then keep only the rows where the right table's key `IS NULL`.
- When counting with an outer join, use `COUNT(o.OrderId)`, not `COUNT(*)`, so a customer with no orders counts as 0.
- Put the table you want to keep first and write `LEFT JOIN`; every `RIGHT JOIN` can be turned round into one.

An inner join drops the rows that have no match. An **outer join** keeps them, and fills the other table's columns with `NULL`.

## LEFT and RIGHT

In a join, the table named first (after `FROM`) is the **left** table, and the joined one is the **right** table.

**`LEFT JOIN`** (also written `LEFT OUTER JOIN`) keeps **every row of the left table**, matched or not:

```sql
SELECT COUNT(*) AS Rows
FROM Customers AS c
LEFT JOIN Orders AS o
    ON c.CustomerId = o.CustomerId;
```

| Rows |
|---|
| 832 |

That's the 830 orders, plus one row for each of the two customers with no orders, with `NULL` in every `Orders` column.

**`RIGHT JOIN`** keeps every row of the **right** table instead. These two give the same rows:

```sql
SELECT COUNT(*) AS Rows
FROM Orders AS o
RIGHT JOIN Customers AS c
    ON o.CustomerId = c.CustomerId;
```

Most people write `LEFT JOIN` and put the table they want to keep first. It reads more naturally, and every `RIGHT JOIN` can be turned round into one.

## Finding what's missing

The `NULL`s are useful. Which customers have never ordered? Keep every customer with a `LEFT JOIN`, then keep only the rows where no order matched:

```sql
SELECT c.CompanyName, o.OrderId
FROM Customers AS c
LEFT JOIN Orders AS o
    ON c.CustomerId = o.CustomerId
WHERE o.OrderId IS NULL;
```

| CompanyName | OrderId |
|---|---|
| FISSA Fabrica Inter. Salchichas S.A. | NULL |
| Paris spécialités | NULL |

This *LEFT JOIN, then IS NULL* pattern answers every "which ones have none?" question: products never ordered, employees with no orders, categories with no products.

## Counting with an outer join

To count orders per customer, including customers with none, use `COUNT` on a column of the right table. `COUNT(*)` would count the `NULL` row as 1; `COUNT(o.OrderId)` skips it:

```sql
SELECT TOP (4) c.CompanyName, COUNT(o.OrderId) AS Orders
FROM Customers AS c
LEFT JOIN Orders AS o
    ON c.CustomerId = o.CustomerId
GROUP BY c.CompanyName
ORDER BY Orders, c.CompanyName;
```

| CompanyName | Orders |
|---|---|
| FISSA Fabrica Inter. Salchichas S.A. | 0 |
| Paris spécialités | 0 |
| Centro comercial Moctezuma | 1 |
| GROSELLA-Restaurante | 2 |

## FULL OUTER JOIN

**`FULL OUTER JOIN`** keeps unmatched rows from **both** tables. Which customers and suppliers share a city?

```sql
SELECT COALESCE(c.City, s.City) AS City, c.CompanyName AS Customer, s.CompanyName AS Supplier
FROM Customers AS c
FULL OUTER JOIN Suppliers AS s
    ON c.City = s.City
WHERE COALESCE(c.City, s.City) IN ('Berlin', 'Madrid', 'Tokyo')
ORDER BY City, Customer;
```

| City | Customer | Supplier |
|---|---|---|
| Berlin | Alfreds Futterkiste | Heli Süßwaren GmbH & Co. KG |
| Madrid | Bólido Comidas preparadas | NULL |
| Madrid | FISSA Fabrica Inter. Salchichas S.A. | NULL |
| Madrid | Romero y tomillo | NULL |
| Tokyo | NULL | Tokyo Traders |

Berlin has both; Madrid has customers but no supplier; Tokyo has a supplier but no customer. `COALESCE(c.City, s.City)` takes whichever city isn't `NULL`.

| Join | Keeps every row of |
|---|---|
| `INNER JOIN` | Neither: only matches |
| `LEFT JOIN` | The left table |
| `RIGHT JOIN` | The right table |
| `FULL OUTER JOIN` | Both tables |

## 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 `ProductId` and `ProductName`.
- `OrderDetails`: one row per order line, with its `OrderId`, the `ProductId` it sold, and the `Quantity`.

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

| ProductId | ProductName |
|---|---|
| 1 | Chai |
| 2 | Chang |
| 3 | Aniseed Syrup |
| 4 | Chef Anton's Cajun Seasoning |
| 5 | Chef Anton's Gumbo Mix |
| 6 | Grandma's Boysenberry Spread |

| OrderId | ProductId | 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 `OrderDetails` has `ProductId` `3`, so a left join gives Aniseed Syrup one row with `NULL` in every `OrderDetails` column. That `NULL` is how you know it was never ordered.

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

| ProductId | ProductName |
|---|---|
| 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, `ProductId` and `ProductName`, in that order, from `Products`.
- Keep every product, matched or not, with a `LEFT JOIN` from `Products` to `OrderDetails`.
- Keep only the rows where no order line matched.
- Sort the rows by `ProductName`, A to Z.

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

## Check yourself

2 questions to try on the lesson page.

## So what

Outer joins keep the rows an inner join would quietly drop, and the `NULL`s they leave behind point straight at what's missing: the customer who never ordered, the product nobody bought.

## Key terms

- [LEFT OUTER JOIN](https://www.ask-codey.com/glossary/left-outer-join)
- [RIGHT OUTER JOIN](https://www.ask-codey.com/glossary/right-outer-join)
- [FULL OUTER JOIN](https://www.ask-codey.com/glossary/full-outer-join)

## Cheat sheets

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

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