A join in SQL Server combines rows from two or more tables based on a related column. The type of join decides which rows survive: an INNER JOIN keeps only matching pairs, while the outer joins (LEFT, RIGHT, FULL) also keep rows that have no match on the other side. In this guide you will learn every join type in SQL Server 2022 with one running example — a Customers and Orders schema — plus self-joins, cross joins, the semi/anti-join patterns, how to find unmatched rows, joining on multiple columns, and a performance note. Every query uses the same seed data so you can copy them into SQL Server Management Studio or Azure Data Studio and see the exact result rows shown.
Set up the sample tables and seed data
Every query below runs against the same two tables. Create them once and seed a few rows, including a customer with no orders and an order tied to a customer we deliberately keep unmatched.
CREATE TABLE dbo.Customers
(
CustomerId INT PRIMARY KEY,
Name NVARCHAR(60) NOT NULL,
City NVARCHAR(60) NOT NULL
);
CREATE TABLE dbo.Orders
(
OrderId INT PRIMARY KEY,
CustomerId INT NULL,
Amount DECIMAL(10,2) NOT NULL
);
INSERT INTO dbo.Customers VALUES
(1, N'Asha', N'Pune'),
(2, N'Ben', N'London'),
(3, N'Chen', N'Singapore'); -- Chen has no orders
INSERT INTO dbo.Orders VALUES
(100, 1, 250.00),
(101, 1, 90.00),
(102, 2, 400.00),
(103, NULL, 60.00); -- order with no customer
INNER JOIN: keep only matching rows
INNER JOIN returns rows only where the join condition is true on both sides. Chen and the orphan order 103 disappear because neither has a partner.
SELECT c.Name, o.OrderId, o.Amount
FROM dbo.Customers AS c
INNER JOIN dbo.Orders AS o ON o.CustomerId = c.CustomerId;
Name OrderId Amount
---- ------- ------
Asha 100 250.00
Asha 101 90.00
Ben 102 400.00
LEFT (OUTER) JOIN: keep all left rows
LEFT OUTER JOIN (the OUTER keyword is optional) returns every row from the left table and fills in NULL where the right table has no match. Chen now appears with NULL order columns.
SELECT c.Name, o.OrderId, o.Amount
FROM dbo.Customers AS c
LEFT JOIN dbo.Orders AS o ON o.CustomerId = c.CustomerId;
Name OrderId Amount
---- ------- ------
Asha 100 250.00
Asha 101 90.00
Ben 102 400.00
Chen NULL NULL
RIGHT (OUTER) JOIN: keep all right rows
RIGHT OUTER JOIN is the mirror image: every row from the right table survives, with NULLs for missing left matches. Here order 103, whose CustomerId is NULL, appears with no customer name. In practice most teams standardize on LEFT JOIN and reorder the tables, because it reads more naturally.
SELECT c.Name, o.OrderId, o.Amount
FROM dbo.Customers AS c
RIGHT JOIN dbo.Orders AS o ON o.CustomerId = c.CustomerId;
Name OrderId Amount
---- ------- ------
Asha 100 250.00
Asha 101 90.00
Ben 102 400.00
NULL 103 60.00
FULL OUTER JOIN: keep unmatched rows from both sides
FULL OUTER JOIN combines the behaviour of LEFT and RIGHT: it returns matched rows once, plus unmatched rows from both tables padded with NULLs. You see Chen (customer without an order) and order 103 (order without a customer) in the same result set. This makes it the natural choice for reconciliation — comparing two data sets to find records that exist on one side but not the other.
SELECT c.Name, o.OrderId, o.Amount
FROM dbo.Customers AS c
FULL OUTER JOIN dbo.Orders AS o ON o.CustomerId = c.CustomerId;
Name OrderId Amount
---- ------- ------
Asha 100 250.00
Asha 101 90.00
Ben 102 400.00
Chen NULL NULL
NULL 103 60.00
CROSS JOIN: every combination
A CROSS JOIN produces the Cartesian product — every left row paired with every right row — and takes no ON clause. With 3 customers and 4 orders it returns 12 rows (3 × 4). It is useful for generating combinations such as sizes × colors or dates × stores, and for building calendar or number tables. Guard against it appearing accidentally, though: an old-style comma join with a forgotten WHERE condition silently becomes a cross join and can explode row counts.
SELECT c.Name, o.OrderId
FROM dbo.Customers AS c
CROSS JOIN dbo.Orders AS o; -- 3 x 4 = 12 rows
SELF JOIN: relate a table to itself
A self join joins a table to another instance of itself, using table aliases to tell the two copies apart. The classic case is an employee/manager hierarchy where ManagerId points back into the same Employees table. A LEFT JOIN keeps the top-level manager whose ManagerId is NULL, whereas an INNER JOIN would drop that root row.
CREATE TABLE dbo.Employees
(
EmployeeId INT PRIMARY KEY,
Name NVARCHAR(60) NOT NULL,
ManagerId INT NULL
);
INSERT INTO dbo.Employees VALUES
(1, N'Devi', NULL), -- CEO, no manager
(2, N'Eli', 1),
(3, N'Fay', 2);
SELECT e.Name AS Employee, m.Name AS Manager
FROM dbo.Employees AS e
LEFT JOIN dbo.Employees AS m ON m.EmployeeId = e.ManagerId;
Employee Manager
-------- -------
Devi NULL
Eli Devi
Fay Eli
Find unmatched rows with LEFT JOIN ... IS NULL
To find rows that have no match — customers who never ordered — do a LEFT JOIN and filter for NULL on the right side's key. This is the anti-join pattern.
SELECT c.Name
FROM dbo.Customers AS c
LEFT JOIN dbo.Orders AS o ON o.CustomerId = c.CustomerId
WHERE o.CustomerId IS NULL; -- returns Chen
Semi-joins and anti-joins with EXISTS / NOT EXISTS
A semi-join returns left rows that have at least one match, without duplicating them or pulling right-side columns. In T-SQL you express it with EXISTS. Its opposite, the anti-join, uses NOT EXISTS.
-- Semi-join: customers who have placed an order
SELECT c.Name FROM dbo.Customers AS c
WHERE EXISTS (SELECT 1 FROM dbo.Orders AS o
WHERE o.CustomerId = c.CustomerId);
-- Anti-join: customers who have not
SELECT c.Name FROM dbo.Customers AS c
WHERE NOT EXISTS (SELECT 1 FROM dbo.Orders AS o
WHERE o.CustomerId = c.CustomerId);
EXISTS is often clearer than INNER JOIN + DISTINCT when you only need existence, and NOT EXISTS handles NULLs more safely than NOT IN. The SQL Server 2022 optimizer frequently produces the same plan for EXISTS and an equivalent join, so choose whichever reads best.
Join on multiple columns
When the relationship spans more than one column, AND the conditions together in the ON clause. This is common with composite keys or tenant-scoped data.
SELECT *
FROM dbo.OrderLines AS ol
INNER JOIN dbo.Products AS p
ON p.TenantId = ol.TenantId
AND p.ProductId = ol.ProductId;
Performance and indexing note
Joins run fastest when the columns in the ON clause are indexed. Make sure foreign-key columns like Orders.CustomerId have a supporting index; the primary key already covers Customers.CustomerId. Without one, SQL Server may resort to hash or nested-loop scans over the full table.
CREATE INDEX IX_Orders_CustomerId ON dbo.Orders(CustomerId);
Keep ON conditions on raw columns rather than wrapping them in functions (ON UPPER(a.Code) = b.Code defeats the index). Put filters that belong to an outer table's match in the ON clause, and filters that restrict the final result in WHERE — mixing them up can silently turn a LEFT JOIN back into an inner one, because a WHERE predicate on the right table's column rejects the NULL-padded rows the outer join just produced.
When a join feels slow, inspect the actual execution plan (SET STATISTICS IO ON, or the graphical plan in SSMS). SQL Server 2022 picks between nested-loop, merge, and hash join operators based on row estimates and available indexes; a missing index or stale statistics is the usual reason it chooses a costly scan. Adding the covering index above and running UPDATE STATISTICS often turns a hash join over full scans into a cheap index seek.
Key takeaways
INNER JOINkeeps only matching pairs;LEFT,RIGHT, andFULL OUTERalso keep unmatched rows padded withNULL.LEFT JOIN ... WHERE right.key IS NULLis the idiomatic way to find rows with no match.CROSS JOINreturns the Cartesian product and takes noONclause.- A
SELF JOINuses two aliases of one table for hierarchies like employee/manager. EXISTS(semi-join) andNOT EXISTS(anti-join) test for matches without duplicating rows.- Index the columns in your
ONclause and keep them function-free so SQL Server 2022 can seek.
Frequently asked questions
What is the difference between INNER JOIN and LEFT JOIN?
INNER JOIN returns only rows that match in both tables. LEFT JOIN returns all rows from the left table and fills right-side columns with NULL when there is no match.
Is JOIN the same as INNER JOIN in SQL Server?
Yes. Writing JOIN with no qualifier means INNER JOIN. Similarly LEFT JOIN, RIGHT JOIN, and FULL JOIN are shorthands for their OUTER forms.
How do I find rows in one table that have no match in another?
Use a LEFT JOIN and filter WHERE otherTable.key IS NULL, or write NOT EXISTS with a correlated subquery. Both express an anti-join.
When should I use EXISTS instead of a JOIN?
Use EXISTS when you only need to know whether a match exists and do not want columns from the other table or duplicate rows. It avoids the DISTINCT you would otherwise add after an INNER JOIN.
Comments (0)
No comments yet — be the first to share your thoughts.