Types of SQL Server Joins with Examples

Joins combine rows from two tables based on a related column — and choosing the right join type is the difference between a correct report and one that silently drops customers. This tutorial works through every SQL Server join type on one small dataset, with the actual result of each query, so you can see exactly which rows survive each join.

The sample data

Two tables: five customers, six orders. Crucially, John and Sara have no orders — how each join treats them is the whole story.

CREATE TABLE Customers (
    CustomerId INT IDENTITY PRIMARY KEY,
    Name       NVARCHAR(50) NOT NULL,
    City       NVARCHAR(50) NOT NULL
);

CREATE TABLE Orders (
    OrderId    INT IDENTITY PRIMARY KEY,
    CustomerId INT NOT NULL REFERENCES Customers(CustomerId),
    OrderDate  DATE NOT NULL,
    Amount     DECIMAL(10,2) NOT NULL
);

INSERT INTO Customers (Name, City) VALUES
 ('Asha','Pune'), ('Rahul','Mumbai'), ('Meera','Pune'),
 ('John','London'), ('Sara','New York');

INSERT INTO Orders (CustomerId, OrderDate, Amount) VALUES
 (1,'2026-06-02',1200.00), (1,'2026-06-20',450.00),
 (2,'2026-06-05',2300.00), (3,'2026-06-11',780.00),
 (3,'2026-07-01',320.00),  (3,'2026-07-15',990.00);

The full setup script is in the companion repo — every query below runs against it as-is.

INNER JOIN — only matching rows

SELECT c.Name, o.OrderDate, o.Amount
FROM Customers c
INNER JOIN Orders o ON o.CustomerId = c.CustomerId
ORDER BY c.Name, o.OrderDate;

Name   OrderDate    Amount
-----  ----------   -------
Asha   2026-06-02   1200.00
Asha   2026-06-20    450.00
Meera  2026-06-11    780.00
Meera  2026-07-01    320.00
Meera  2026-07-15    990.00
Rahul  2026-06-05   2300.00

Six rows — one per order. John and Sara vanish: an INNER JOIN keeps only rows with a match on both sides. That's correct for "show me orders with their customer", and silently wrong for "show me all customers with their orders". (JOIN alone means INNER JOIN — the keyword is optional.)

LEFT JOIN — everything from the left, matches from the right

SELECT c.Name, o.OrderId, o.Amount
FROM Customers c
LEFT JOIN Orders o ON o.CustomerId = c.CustomerId
ORDER BY c.Name;

Name   OrderId  Amount
-----  -------  -------
Asha   1        1200.00
Asha   2         450.00
John   NULL     NULL
Meera  4         780.00
Meera  5         320.00
Meera  6         990.00
Rahul  3        2300.00
Sara   NULL     NULL

All five customers appear; the orderless ones carry NULLs in every Orders column. This is the join for "all of X, with Y where it exists" — the most common reporting shape.

The anti-join: LEFT JOIN … IS NULL

Filter on those NULLs and you get the rows without a match — customers who never ordered:

SELECT c.Name, c.City
FROM Customers c
LEFT JOIN Orders o ON o.CustomerId = c.CustomerId
WHERE o.OrderId IS NULL;

Name  City
----  --------
John  London
Sara  New York

The same question can be asked with NOT EXISTS, which reads more directly and optimizes at least as well:

SELECT Name, City FROM Customers c
WHERE NOT EXISTS (SELECT 1 FROM Orders o WHERE o.CustomerId = c.CustomerId);

A classic trap: putting a right-table condition in WHERE instead of ON turns a LEFT JOIN back into an INNER JOIN. LEFT JOIN Orders o ON … WHERE o.Amount > 500 discards the NULL rows (NULL isn't > 500). If the condition should only filter matches, put it in the ON clause.

RIGHT JOIN — the mirror

RIGHT JOIN keeps everything from the right table instead. It's exactly LEFT JOIN with the table order flipped, and that's how almost everyone writes it — consistently using LEFT keeps queries readable. You'll meet RIGHT JOIN mostly in code you inherit.

FULL OUTER JOIN — everything from both sides

Keeps all rows from both tables, matching where possible:

SELECT c.Name, o.OrderId
FROM Customers c
FULL OUTER JOIN Orders o ON o.CustomerId = c.CustomerId
WHERE c.CustomerId IS NULL OR o.OrderId IS NULL;

Name  OrderId
----  -------
John  NULL
Sara  NULL

With the NULL filter this becomes a data-quality query: rows on either side missing their partner. (Here only orphaned customers exist — the foreign key prevents orphaned orders, which is exactly what constraints are for.) FULL OUTER is rare in application code but invaluable when reconciling two datasets.

CROSS JOIN — every combination

No ON clause; every left row pairs with every right row:

SELECT COUNT(*) AS combinations FROM Customers CROSS JOIN Orders;
-- 30   (5 customers × 6 orders)

Deliberate uses: generating a calendar × product matrix, test-data generation, pairing every size with every color. Accidental uses: forgetting a join condition — if a query suddenly returns millions of rows, look for a missing ON.

SELF JOIN — a table joined to itself

Not a separate keyword, just the same table twice with aliases. The classic case is a hierarchy — employees and their managers in one table:

SELECT e.Name AS Employee, m.Name AS Manager
FROM Employees e
INNER JOIN Employees m ON e.ManagerId = m.EmployeeId
ORDER BY m.Name, e.Name;

Employee  Manager
--------  -------
Divya     Amit
Vikram    Amit
Rohan     Neha
Tara      Neha
Amit      Priya
Neha      Priya

Walking the whole hierarchy at once (any depth) needs a recursive common table expression.

Where joins bite — disadvantages and common mistakes

Joins are the workhorse of SQL, but they are not free, and the wrong join produces answers that look right. These are the failure modes that actually show up in code review.

Fan-out: one-to-many joins multiply rows

Every customer-with-orders join returns one row per order, not per customer. Aggregate over that and you count the wrong thing:

-- "How many customers per city?" — looks reasonable, is wrong.
SELECT c.City, COUNT(*) AS Customers
FROM Customers c
LEFT JOIN Orders o ON o.CustomerId = c.CustomerId
GROUP BY c.City;

City      Customers
--------  ---------
London    1
Mumbai    1
New York  1
Pune      5     -- Pune has TWO customers, not five

Asha (2 orders) and Meera (3 orders) inflate Pune to 5. Joining a second child table (say, Payments) makes it worse — the two child sets cross-multiply per customer and SUMs double-count. Aggregate each child in its own subquery (or CROSS APPLY) before joining, and use COUNT(DISTINCT c.CustomerId) only as a last resort.

DISTINCT is a symptom, not a fix

If a query needs SELECT DISTINCT to look right, a join is almost certainly fanning out rows. DISTINCT hides the duplication, makes the server sort/hash the whole result to de-duplicate it, and still breaks the moment someone adds a SUM. Find the join that multiplies rows and fix that instead.

NULL join keys never match

NULL = NULL is not true in SQL. If Orders.CustomerId allowed NULLs, those orders would silently vanish from an INNER JOIN in both directions — they're not "unmatched", they're invisible. Keep join keys NOT NULL (as this schema does), or handle the NULL case explicitly.

Filtering an outer join in WHERE

The trap from the LEFT JOIN section is worth repeating because it's the most common join bug in production: a right-table condition in WHERE (WHERE o.Amount > 500) throws away the NULL rows and silently turns your LEFT JOIN into an INNER JOIN. John and Sara disappear from a report that promised all customers. Conditions that should only filter matches belong in ON.

A missing ON clause is a Cartesian product

Five customers × six orders is 30 rows; two million × ten million is a query that takes the server down. Old-style comma joins (FROM Customers c, Orders o WHERE …) make this easy to do by accident — one forgotten WHERE and you have a full CROSS JOIN. Write explicit JOIN … ON and the parser refuses to run without a condition.

Joins have a real runtime cost

Each join is extra work at execution time — a nested-loops, hash, or merge operation the optimizer must pick and size correctly:

• Unindexed join columns force scans. Every query in this article touches Orders.CustomerId; without an index each join reads the whole table. SQL Server does not index foreign keys automatically.
• Many-table joins degrade plans. Beyond a handful of tables the optimizer's search space explodes and estimates get worse; a bad row estimate on join three poisons every join after it. If a report needs 12 joins, consider a pre-aggregated reporting table or an indexed view.
• Big hash joins spill. Joining two large sets with no useful index hashes one side into memory — undersized grants spill to tempdb and the query crawls.

None of this means "avoid joins" — it means keep join keys indexed, join on as few tables as the question needs, and check the actual execution plan when a join-heavy query is slow.

Semi-joins read better as EXISTS

"Customers who have at least one order" doesn't need the orders themselves. A join answers it with fan-out and a DISTINCT; EXISTS answers it directly, stops at the first match, and can't duplicate rows:

SELECT Name FROM Customers c
WHERE EXISTS (SELECT 1 FROM Orders o WHERE o.CustomerId = c.CustomerId);

Choosing, in one table

Two habits that prevent most join bugs: always qualify columns with table aliases (ambiguity errors appear the day someone adds a same-named column), and index your foreign key columns — every join in this article hits Orders.CustomerId, and without an index each one is a table scan.