SQL Server joins guide

A SQL Server JOIN combines rows from two table sources. The join predicate says how the rows relate. In most day-to-day queries that means matching a foreign key to a primary key, such as Orders.CustomerID to Customers.CustomerID.

The logical join type decides which rows can appear in the result: matching rows only, all rows from one side, all rows from both sides, or every possible combination. SQL Server then chooses a physical join algorithm to run the query. Microsoft documents nested loops, merge, hash, and adaptive joins, but those are execution choices. They do not change the logical meaning of your query.

The main types of SQL join in SQL Server are INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, and CROSS JOIN. MSSQL is common shorthand for Microsoft SQL Server, so a join MSSQL example usually means the same T-SQL joins, sometimes typed as T SQL joins, shown here.

JOIN without another keyword means INNER JOIN. Write INNER JOIN when clarity matters. Future readers should not need to remember default syntax while debugging a production report.

The examples below use temporary tables so the queries are runnable in SQL Server Management Studio or Azure Data Studio. The sample data intentionally includes customers with no orders, an order with a missing customer, and orders with multiple lines.

That gives the joins something useful to show. Perfect demo data makes joins look cleaner than real reporting data. Real data usually contains missing rows, optional relationships, one-to-many joins, and old records that do not fit the neat diagram.

IF OBJECT_ID('tempdb..#OrderLines') IS NOT NULL DROP TABLE #OrderLines;
IF OBJECT_ID('tempdb..#Orders') IS NOT NULL DROP TABLE #Orders;
IF OBJECT_ID('tempdb..#Products') IS NOT NULL DROP TABLE #Products;
IF OBJECT_ID('tempdb..#Customers') IS NOT NULL DROP TABLE #Customers;
IF OBJECT_ID('tempdb..#CalendarDates') IS NOT NULL DROP TABLE #CalendarDates;

CREATE TABLE #Customers
(
    CustomerID int NOT NULL PRIMARY KEY,
    CustomerName varchar(40) NOT NULL,
    Region varchar(20) NOT NULL
);

CREATE TABLE #Orders
(
    OrderID int NOT NULL PRIMARY KEY,
    CustomerID int NOT NULL,
    OrderDate date NOT NULL,
    Status varchar(20) NOT NULL
);

CREATE TABLE #Products
(
    ProductID int NOT NULL PRIMARY KEY,
    ProductName varchar(40) NOT NULL,
    IsActive bit NOT NULL
);

CREATE TABLE #OrderLines
(
    OrderLineID int NOT NULL PRIMARY KEY,
    OrderID int NOT NULL,
    ProductID int NOT NULL,
    Quantity int NOT NULL
);

CREATE TABLE #CalendarDates
(
    CalendarDate date NOT NULL PRIMARY KEY
);

INSERT INTO #Customers (CustomerID, CustomerName, Region)
VALUES
    (1, 'Acme Ltd', 'North'),
    (2, 'Blue Street', 'South'),
    (3, 'No Orders Co', 'North'),
    (4, 'Quiet Shop', 'West');

INSERT INTO #Orders (OrderID, CustomerID, OrderDate, Status)
VALUES
    (1001, 1, '2026-05-01', 'Paid'),
    (1002, 1, '2026-05-03', 'Open'),
    (1003, 2, '2026-05-04', 'Paid'),
    (1004, 99, '2026-05-05', 'Open');

INSERT INTO #Products (ProductID, ProductName, IsActive)
VALUES
    (10, 'Backup review', 1),
    (11, 'Performance review', 1),
    (12, 'Retired report', 0);

INSERT INTO #OrderLines (OrderLineID, OrderID, ProductID, Quantity)
VALUES
    (1, 1001, 10, 1),
    (2, 1001, 11, 2),
    (3, 1002, 11, 1),
    (4, 1003, 10, 1);

INSERT INTO #CalendarDates (CalendarDate)
VALUES
    ('2026-05-01'),
    ('2026-05-02'),
    ('2026-05-03');

An INNER JOIN returns rows where the join predicate matches on both sides. If a customer has no orders, that customer is not returned. If an order points to a missing customer, that order is not returned either.

This is the common SQL Server join for normal lookups: orders with customers, order lines with products, users with departments, and other relationships where unmatched rows are not useful for the result. If someone searches for MSSQL inner join, this is the SQL Server INNER JOIN pattern they usually need.

SELECT
    c.CustomerName,
    o.OrderID,
    o.OrderDate,
    o.Status
FROM #Customers AS c
INNER JOIN #Orders AS o
    ON o.CustomerID = c.CustomerID
ORDER BY c.CustomerName, o.OrderDate;

Multiple inner joins keep narrowing the result to rows that match across all joined tables. That is often correct, but it can also hide missing data. If a report suddenly loses rows after another join is added, check which relationship is no longer optional.

SELECT
    c.CustomerName,
    o.OrderID,
    p.ProductName,
    ol.Quantity
FROM #Customers AS c
INNER JOIN #Orders AS o
    ON o.CustomerID = c.CustomerID
INNER JOIN #OrderLines AS ol
    ON ol.OrderID = o.OrderID
INNER JOIN #Products AS p
    ON p.ProductID = ol.ProductID
ORDER BY c.CustomerName, o.OrderID, p.ProductName;

A LEFT JOIN returns all rows from the left table and matching rows from the right table. When no right-side row exists, SQL Server returns NULL for the right-side columns.

Use a left join in SQL Server when the left-side entity must stay in the result: all customers, all products, all databases, all jobs, all expected dates. The right side is optional.

SELECT
    c.CustomerName,
    o.OrderID,
    o.Status
FROM #Customers AS c
LEFT JOIN #Orders AS o
    ON o.CustomerID = c.CustomerID
ORDER BY c.CustomerName, o.OrderID;

The common mistake is filtering the right-side table in the WHERE clause. That can remove the NULL rows and turn the practical result into an inner join.

-- Keeps all customers, then matches only paid orders.
SELECT
    c.CustomerName,
    o.OrderID,
    o.Status
FROM #Customers AS c
LEFT JOIN #Orders AS o
    ON o.CustomerID = c.CustomerID
   AND o.Status = 'Paid'
ORDER BY c.CustomerName, o.OrderID;

-- This removes customers without a paid order.
-- It behaves like an INNER JOIN for this condition.
SELECT
    c.CustomerName,
    o.OrderID,
    o.Status
FROM #Customers AS c
LEFT JOIN #Orders AS o
    ON o.CustomerID = c.CustomerID
WHERE o.Status = 'Paid'
ORDER BY c.CustomerName, o.OrderID;

A RIGHT JOIN preserves rows from the right table and returns matching rows from the left table. It is valid SQL Server outer join syntax, but in normal work it is usually clearer to swap the table order and write a LEFT JOIN.

People read joins from left to right. If the preserved table matters, put it first. That keeps the query easier to review when the FROM clause grows.

-- Same result as a LEFT JOIN after swapping table order.
SELECT
    c.CustomerName,
    o.OrderID,
    o.Status
FROM #Orders AS o
RIGHT JOIN #Customers AS c
    ON c.CustomerID = o.CustomerID
ORDER BY c.CustomerName, o.OrderID;

A FULL OUTER JOIN returns matching rows plus unmatched rows from both sides. It is useful for reconciliation, audit queries, and finding rows that exist in one source but not the other.

People often search for full join in SQL Server or full join MSSQL. In T-SQL, use FULL OUTER JOIN. It is less common for normal lookup queries. If the result has many nullable columns from both sides, check whether you are comparing sets or trying to build a daily report with the wrong join.

SELECT
    c.CustomerID AS CustomerTableID,
    c.CustomerName,
    o.OrderID,
    o.CustomerID AS OrderTableCustomerID,
    o.Status
FROM #Customers AS c
FULL OUTER JOIN #Orders AS o
    ON o.CustomerID = c.CustomerID
WHERE c.CustomerID IS NULL
   OR o.CustomerID IS NULL
ORDER BY COALESCE(c.CustomerID, o.CustomerID), o.OrderID;

APPLY is a T-SQL table operator used in the FROM clause. It lets the right-side table expression reference columns from the left side. That makes it useful for top-N-per-row queries, table-valued functions, and correlated row sets.

CROSS APPLY behaves like an inner join against the applied row set: if the right side returns nothing, the left row disappears. OUTER APPLY behaves like a left join against the applied row set: the left row stays and right-side columns become NULL. Searches for cross apply SQL Server, outer apply SQL Server, or outer apply SQL usually refer to this T-SQL pattern.

-- CROSS APPLY returns customers with a matching row from the inner query.
SELECT
    c.CustomerName,
    latest_order.OrderID,
    latest_order.OrderDate,
    latest_order.Status
FROM #Customers AS c
CROSS APPLY
(
    SELECT TOP (1)
        o.OrderID,
        o.OrderDate,
        o.Status
    FROM #Orders AS o
    WHERE o.CustomerID = c.CustomerID
    ORDER BY o.OrderDate DESC, o.OrderID DESC
) AS latest_order
ORDER BY c.CustomerName;

-- OUTER APPLY preserves customers without a matching order.
SELECT
    c.CustomerName,
    latest_order.OrderID,
    latest_order.OrderDate,
    latest_order.Status
FROM #Customers AS c
OUTER APPLY
(
    SELECT TOP (1)
        o.OrderID,
        o.OrderDate,
        o.Status
    FROM #Orders AS o
    WHERE o.CustomerID = c.CustomerID
    ORDER BY o.OrderDate DESC, o.OrderID DESC
) AS latest_order
ORDER BY c.CustomerName;

The ON clause is where the join relationship belongs. Searches for SQL JOIN ON usually mean this part of the query: the predicate that tells SQL Server how two table sources match. Keep join predicates there so the relationship is visible before other filters. Microsoft also recommends specifying join conditions in the FROM clause because it separates join logic from search conditions.

Good join predicates use the actual key relationship. Weak predicates join on names, descriptions, dates, or partial business labels because those columns happen to look similar. That works until spelling changes, duplicates appear, or two regions use the same label.

SELECT
    c.CustomerName,
    o.OrderID,
    o.OrderDate
FROM #Customers AS c
INNER JOIN #Orders AS o
    ON o.CustomerID = c.CustomerID
   AND o.OrderDate >= '2026-05-01'
   AND o.OrderDate <  '2026-06-01';

Duplicate rows after a join usually mean the query changed grain. Joining orders to order lines moves the result from one row per order to one row per order line. That is not SQL Server being weird. That is the relationship doing exactly what it was asked to do.

DISTINCT can hide duplicate output, but it does not fix a bad join. First decide the intended grain: one row per customer, one row per order, one row per product per date, or one row per order line. Then join and aggregate to that grain.

SELECT
    o.OrderID,
    COUNT(*) AS JoinedRows
FROM #Orders AS o
INNER JOIN #OrderLines AS ol
    ON ol.OrderID = o.OrderID
GROUP BY o.OrderID
HAVING COUNT(*) > 1;

A JOIN combines columns side by side from related table sources. UNION stacks compatible result sets. MERGE changes target data based on another source. PIVOT reshapes row values into columns. APPLY evaluates a right-side table expression for each left-side row.

Those features sometimes appear in the same search because people are trying to merge tables or reshape report data. If you search for SQL merge tables, check whether you really need a join, a UNION, or the MERGE statement. For MS SQL pivot questions, keep the intent separate: PIVOT reshapes data, while joins retrieve related columns.

Join performance starts with correct row counts and useful access paths. Indexes on join keys matter. Matching data types matter. Accurate statistics matter. The actual execution plan will show whether SQL Server used nested loops, merge, hash, or adaptive join behavior.

Do not tune a join only from the keyword. A slow INNER JOIN might be a missing index, bad estimate, implicit conversion, stale statistics, parameter-sensitive plan, oversized result, blocking chain, or memory grant issue.

Related reads: SQL Server indexing, SQL Server waits, and SQL Server blocking.