SQL Server Joins

Joins combine rows from two or more tables based on a related column. SQL Server supports the full standard set -- INNER, LEFT, RIGHT, FULL, and CROSS -- plus one operator most other databases lack: APPLY, which joins a table to a correlated table expression. This guide covers all of them, the classic mistakes, and how the optimizer actually executes joins.

Sample Schema

CREATE TABLE customers (
    customer_id int          PRIMARY KEY,
    name        varchar(50)  NOT NULL,
    region      varchar(10)  NOT NULL
);
 
CREATE TABLE orders (
    order_id    int   PRIMARY KEY,
    customer_id int   NULL,          -- NULL = guest checkout
    amount      int   NOT NULL,
    order_date  date  NOT NULL
);
 
INSERT INTO customers VALUES
    (1, 'Alice',   'north'),
    (2, 'Bruno',   'south'),
    (3, 'Carmen',  'north');
 
INSERT INTO orders VALUES
    (101, 1,    250, '2026-01-05'),
    (102, 1,    120, '2026-01-20'),
    (103, 2,    400, '2026-02-02'),
    (104, NULL,  80, '2026-02-10');

Carmen has no orders. Order 104 has no customer. Every join type below treats those two rows differently.

INNER JOIN

Returns only rows with a match on both sides:

SELECT c.name, o.order_id, o.amount
FROM customers AS c
INNER JOIN orders AS o
    ON o.customer_id = c.customer_id;

name    order_id  amount
Alice   101       250
Alice   102       120
Bruno   103       400

Carmen (no orders) and order 104 (no customer) both disappear. The INNER keyword is optional -- JOIN alone means INNER JOIN -- but writing it out makes intent explicit.

LEFT JOIN

Keeps every row from the left table; unmatched right-side columns come back as NULL:

SELECT c.name, o.order_id, o.amount
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id;

name    order_id  amount
Alice   101       250
Alice   102       120
Bruno   103       400
Carmen  NULL      NULL

LEFT OUTER JOIN is the same thing; OUTER is optional noise.

The ON vs WHERE Trap

The single most common outer-join bug: putting a right-table filter in WHERE instead of ON. These two queries are not equivalent:

-- Query A: filter in ON -- still a LEFT JOIN
SELECT c.name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
   AND o.amount > 200;
 
-- Query B: filter in WHERE -- silently becomes an INNER JOIN
SELECT c.name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
WHERE o.amount > 200;

Query A returns all three customers; Carmen and Bruno-with-small-orders just get NULL order columns where nothing matched the extra condition. Query B filters after the join -- and since unmatched rows have o.amount = NULL, and NULL > 200 is not true, every unmatched customer is thrown away. The LEFT JOIN degrades into an INNER JOIN.

Rule of thumb: conditions on the right table of a LEFT JOIN belong in ON. Conditions on the left table belong in WHERE. The one deliberate exception is the anti-join pattern below.

RIGHT JOIN and FULL JOIN

RIGHT JOIN is a LEFT JOIN with the tables swapped. It exists, it works, and almost nobody uses it -- rewriting as LEFT JOIN reads better because the "kept" table comes first:

-- These are identical:
SELECT ... FROM customers c RIGHT JOIN orders o ON ...
SELECT ... FROM orders o LEFT JOIN customers c ON ...

FULL JOIN keeps unmatched rows from both sides. Unlike MySQL (which still lacks it) and SQLite (which only added it in 3.39), SQL Server has always supported it natively:

SELECT c.name, o.order_id, o.amount
FROM customers AS c
FULL JOIN orders AS o
    ON o.customer_id = c.customer_id;

name    order_id  amount
Alice   101       250
Alice   102       120
Bruno   103       400
Carmen  NULL      NULL
NULL    104       80

FULL JOIN is the standard tool for reconciliation queries: "show me everything in system A, everything in system B, and flag what only exists on one side."

CROSS JOIN

Every row from the left paired with every row from the right -- a Cartesian product, no ON clause:

SELECT c.name, m.month_start
FROM customers AS c
CROSS JOIN (VALUES ('2026-01-01'), ('2026-02-01')) AS m(month_start);

Legitimate uses are scaffolding queries: generating a row for every customer × month combination before LEFT JOINing actual data onto it, so months with zero sales still appear in a report.

Self-Joins

A table joined to itself -- the classic case is an employee table with a manager_id:

SELECT e.name AS employee, m.name AS manager
FROM employees AS e
LEFT JOIN employees AS m
    ON m.employee_id = e.manager_id;

Aliases are mandatory here; without them the table names are ambiguous. For walking a hierarchy of unknown depth, a self-join only gets you one level -- use a recursive CTE instead.

Anti-Joins: Rows With No Match

"Customers who have never ordered" has three standard spellings in T-SQL:

-- 1. NOT EXISTS (recommended)
SELECT c.name
FROM customers AS c
WHERE NOT EXISTS (
    SELECT 1 FROM orders AS o
    WHERE o.customer_id = c.customer_id
);
 
-- 2. LEFT JOIN ... IS NULL
SELECT c.name
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;
 
-- 3. NOT IN (avoid on nullable columns)
SELECT c.name
FROM customers AS c
WHERE c.customer_id NOT IN (
    SELECT customer_id FROM orders
);

Options 1 and 2 both return Carmen. Option 3 returns nothing -- because orders.customer_id contains a NULL (order 104), and x NOT IN (1, 2, NULL) evaluates to unknown for every x. One NULL in the subquery silently empties the entire result. This is not a SQL Server quirk -- it is how three-valued logic works everywhere -- but it bites hard because it fails silently.

Prefer NOT EXISTS: it is immune to the NULL trap and the optimizer compiles it to a proper anti-semi-join. NOT IN is only safe when the subquery column is declared NOT NULL.

CROSS APPLY and OUTER APPLY

APPLY is T-SQL's distinctive join operator (introduced in SQL Server 2005; PostgreSQL's equivalent is LATERAL). A regular JOIN cannot reference columns from the left table inside the right-side expression. APPLY can -- the right side is re-evaluated for each row of the left side.

The canonical use case: top-N per group.

-- Each customer's 2 largest orders
SELECT c.name, top_orders.order_id, top_orders.amount
FROM customers AS c
CROSS APPLY (
    SELECT TOP (2) o.order_id, o.amount
    FROM orders AS o
    WHERE o.customer_id = c.customer_id   -- correlated: references c
    ORDER BY o.amount DESC
) AS top_orders;

CROSS APPLY behaves like an INNER JOIN: customers whose subquery returns no rows (Carmen) are dropped. OUTER APPLY behaves like a LEFT JOIN: Carmen appears with NULLs.

APPLY is also the idiomatic way to join against a table-valued function, which a plain JOIN cannot do:

SELECT o.order_id, tags.value AS tag
FROM orders AS o
CROSS APPLY STRING_SPLIT(o.tag_csv, ',') AS tags;

For ranking-based alternatives to top-N-per-group, see window functions -- ROW_NUMBER() in a CTE filtered to rn <= 2 produces the same result and sometimes a better plan. Test both on your data.

How SQL Server Executes Joins

The join syntax you write and the physical algorithm the optimizer picks are separate things. There are three physical join operators, visible in the execution plan:

• Nested Loops -- for each outer row, seek into the inner table. Great when the outer side is small and the inner side has an index on the join key.
• Hash Match -- build a hash table on the smaller input, probe with the larger. The workhorse for large, unindexed, or unsorted inputs.
• Merge Join -- both inputs sorted on the join key, interleaved in one pass. Cheapest when the sort order already exists (e.g. both sides clustered on the key).

You normally let the optimizer choose. T-SQL does allow forcing an algorithm with join hints:

SELECT ...
FROM customers AS c
INNER HASH JOIN orders AS o    -- forces hash match
    ON o.customer_id = c.customer_id;

Treat hints as a last resort. A hint that helps today pins the plan forever, including after the data distribution changes and the hint becomes harmful. If the optimizer picks a bad join type, the root cause is usually missing indexes or stale statistics -- fix those first. A missing index on the foreign key column (orders.customer_id here) is the single most common reason a join is slow; see indexes explained.

Legacy Syntax to Avoid

Two things you may meet in old codebases:

• Comma joins: FROM customers c, orders o WHERE o.customer_id = c.customer_id. Works, but mixes join logic into the WHERE clause and turns into an accidental Cartesian product the moment someone forgets a predicate. Use explicit JOIN ... ON.
• Old-style outer joins: WHERE c.customer_id *= o.customer_id. This pre-1992 syntax no longer runs at all -- it raises an error under compatibility level 90 (SQL Server 2005) and later. Rewrite as LEFT JOIN.

Common Mistakes

The duplicate-key explosion deserves a note: joins match every left row with every matching right row. If the key is unique on neither side, 10 × 10 matching rows become 100 output rows. When a query returns more rows than the larger input table, this is almost always why.

Mako's AI autocomplete is handy when writing multi-join queries against an unfamiliar schema -- it knows the foreign-key relationships and suggests the ON clauses.

Mako connects to SQL Server with AI-powered autocomplete. Try it free at mako.ai.