SQL JOINs explained: INNER, LEFT, RIGHT and FULL, with one picture each

You join dbo.Orders to dbo.Employees to show who took each order. The orders table has 5,000 rows. Your report shows 4,000. There is no error and no warning: a thousand orders simply are not there.

Nothing is broken. You asked for an INNER JOIN, and an INNER JOIN only returns rows that have a partner on both sides. Every join type is really the answer to one question: what should happen to rows that have no match? This guide answers it four times, with one picture for each join.

SELECT
    (SELECT COUNT(*) FROM dbo.Orders) AS AllOrders,
    (SELECT COUNT(*)
     FROM dbo.Orders AS o
     INNER JOIN dbo.Employees AS e
         ON e.EmployeeID = o.SalesEmployeeID) AS OrdersAfterInnerJoin;

Two small lists to join

Five thousand rows are too many to see, so the four join-type comparisons use the first ten orders, which we call FirstOrders, and the twelve employees. The join condition is the same each time: the order’s SalesEmployeeID must equal the employee’s EmployeeID.

SELECT OrderID, SalesEmployeeID
FROM dbo.Orders
WHERE OrderID <= 10
ORDER BY OrderID;

The queries below wrap those ten orders in a common table expression (the WITH FirstOrders AS (...) part) so that each one runs on its own. You can read it as “for this query, FirstOrders means these ten rows”.

INNER JOIN: only the pairs

An INNER JOIN returns one row for every pair of rows that satisfies the ON condition. A row with no partner produces nothing, on either side. Writing just JOIN means the same thing.

WITH FirstOrders AS (
    SELECT OrderID, SalesEmployeeID
    FROM dbo.Orders
    WHERE OrderID <= 10
)
SELECT o.OrderID, o.SalesEmployeeID, e.EmployeeID, e.FirstName
FROM FirstOrders AS o
INNER JOIN dbo.Employees AS e
    ON e.EmployeeID = o.SalesEmployeeID
ORDER BY o.OrderID;

Use it when a row without a partner is meaningless for the question, such as “sales per employee”, where an order nobody took cannot be credited to anyone.

LEFT JOIN: keep everything on the left

A LEFT JOIN returns every row from the left table. Where a partner exists, its columns are filled in; where none exists, they are NULL. This is the join to use when the left table is the list you must not lose, such as “all orders, with the employee if there is one”.

WITH FirstOrders AS (
    SELECT OrderID, SalesEmployeeID
    FROM dbo.Orders
    WHERE OrderID <= 10
)
SELECT o.OrderID, o.SalesEmployeeID, e.EmployeeID, e.FirstName
FROM FirstOrders AS o
LEFT JOIN dbo.Employees AS e
    ON e.EmployeeID = o.SalesEmployeeID
ORDER BY o.OrderID;

This is the fix for the report at the top of the page: switch the INNER JOIN on the full table to a LEFT JOIN, and no order is lost.

SELECT COUNT(*) AS OrdersAfterLeftJoin
FROM dbo.Orders AS o
LEFT JOIN dbo.Employees AS e
    ON e.EmployeeID = o.SalesEmployeeID;

RIGHT JOIN: keep everything on the right

A RIGHT JOIN is the mirror image: every row from the right table is kept. Here that answers “all employees, with any of these orders they took”.

WITH FirstOrders AS (
    SELECT OrderID, SalesEmployeeID
    FROM dbo.Orders
    WHERE OrderID <= 10
)
SELECT o.OrderID, o.SalesEmployeeID, e.EmployeeID, e.FirstName
FROM FirstOrders AS o
RIGHT JOIN dbo.Employees AS e
    ON e.EmployeeID = o.SalesEmployeeID
ORDER BY o.OrderID, e.EmployeeID;

WITH FirstOrders AS (
    SELECT OrderID, SalesEmployeeID
    FROM dbo.Orders
    WHERE OrderID <= 10
)
SELECT o.OrderID, o.SalesEmployeeID, e.EmployeeID, e.FirstName
FROM dbo.Employees AS e
LEFT JOIN FirstOrders AS o
    ON o.SalesEmployeeID = e.EmployeeID
ORDER BY o.OrderID, e.EmployeeID;

FULL OUTER JOIN: keep everything

A FULL OUTER JOIN keeps unmatched rows from both sides. It is the join for reconciling two lists, because the gaps on each side are exactly what you are looking for: orders nobody took, and employees who took none of them.

WITH FirstOrders AS (
    SELECT OrderID, SalesEmployeeID
    FROM dbo.Orders
    WHERE OrderID <= 10
)
SELECT o.OrderID, o.SalesEmployeeID, e.EmployeeID, e.FirstName
FROM FirstOrders AS o
FULL OUTER JOIN dbo.Employees AS e
    ON e.EmployeeID = o.SalesEmployeeID
ORDER BY o.OrderID, e.EmployeeID;

Find the rows that did not match

The most useful outer join pattern is not about the matches at all. Keep every row with a LEFT JOIN, then keep only the rows where the right side came back empty. Test the right table’s key, because a key column is never NULL in a real match.

SELECT o.OrderStatus, COUNT(*) AS UnpaidOrders
FROM dbo.Orders AS o
LEFT JOIN dbo.Payments AS p
    ON p.OrderID = o.OrderID
WHERE p.PaymentID IS NULL
GROUP BY o.OrderStatus
ORDER BY o.OrderStatus;

Three mistakes to avoid

1. Filtering the right table in WHERE

Say you want all ten orders, and the employee’s name only when that employee works in Sales (DepartmentID 1). WHERE runs after the join. If it tests a column from the optional table, the NULL rows the LEFT JOIN kept fail the test and disappear, so you get INNER JOIN results while the query still says LEFT JOIN.

WITH FirstOrders AS (
    SELECT OrderID, SalesEmployeeID
    FROM dbo.Orders
    WHERE OrderID <= 10
)
SELECT o.OrderID, o.SalesEmployeeID, e.EmployeeID, e.FirstName
FROM FirstOrders AS o
LEFT JOIN dbo.Employees AS e
    ON e.EmployeeID = o.SalesEmployeeID
WHERE e.DepartmentID = 1
ORDER BY o.OrderID;

WITH FirstOrders AS (
    SELECT OrderID, SalesEmployeeID
    FROM dbo.Orders
    WHERE OrderID <= 10
)
SELECT o.OrderID, o.SalesEmployeeID, e.EmployeeID, e.FirstName
FROM FirstOrders AS o
LEFT JOIN dbo.Employees AS e
    ON e.EmployeeID = o.SalesEmployeeID
   AND e.DepartmentID = 1
ORDER BY o.OrderID;

If you really do want only the orders taken by Sales, the WHERE version returns the right rows, but write it as an INNER JOIN so the query says what it does.

2. Expecting NULL keys to match

A NULL key never matches anything, not even another NULL, because NULL = NULL is unknown rather than true. Orders 5 and 10 found no partner above because no employee has a missing ID, but even a row with a NULL key on the other side would not have paired with them. If missing keys should match a default, decide that explicitly, for example with an “Unassigned” row, rather than hoping the join does it.

3. Joining to a table with many rows per key

Joins do not only drop rows; they can also multiply them. When each left row has several partners, you get one output row per partner.

SELECT COUNT(*) AS JoinedRows,
       COUNT(DISTINCT o.OrderID) AS Orders
FROM dbo.Orders AS o
INNER JOIN dbo.OrderItems AS oi
    ON oi.OrderID = o.OrderID;

If you then add up an order-level amount, the total doubles. Diagnosing duplicate or missing rows after joins shows how to catch this, and 5 SQL mistakes that silently return wrong answers walks through a doubled total.

Check yourself

SELECT COUNT(*) AS PaidOrders
FROM dbo.Orders AS o
INNER JOIN dbo.Payments AS p
    ON p.OrderID = o.OrderID;

SELECT COUNT(*) AS OrderRows
FROM dbo.Orders AS o
LEFT JOIN dbo.Payments AS p
    ON p.OrderID = o.OrderID;

Where to go next

Each join has a full lesson, with more practice, in Chapter 6 of the SQL Server roadmap:

• Join keys and relationship cardinality: why some joins multiply rows
• INNER JOIN
• LEFT and RIGHT OUTER JOIN
• FULL OUTER JOIN and CROSS JOIN
• Self joins: join Employees to itself to find each manager
• Diagnosing duplicate or missing rows after joins