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.

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.