# 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 3, 2026

## 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 |

## 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