SQL Server

SQL Server JOINs in Practice: Multi-Table SELECT, UPDATE, DELETE, Indexing, and Performance

A practical SQL Server guide to joining multiple tables, updating and deleting with JOINs, avoiding duplicate-row mistakes, choosing indexes for join columns, and understanding how JOIN design affects performance.

By JaviPublished 20 min readIntermediate

On this page

JOINs are one of the most important parts of SQL Server.

They are also one of the easiest places to create performance problems.

A JOIN can be logically correct and still be expensive because of:

  • missing indexes;
  • poor cardinality estimates;
  • duplicate matches;
  • many-to-many relationships;
  • implicit conversions;
  • non-SARGable predicates;
  • stale statistics;
  • joining far more rows than necessary;
  • wide SELECT lists;
  • bad filtering order.

This guide focuses on practical JOIN patterns you can use in real SQL Server workloads.

It covers:

  • joining multiple tables;
  • INNER, LEFT, and EXISTS patterns;
  • UPDATE with JOIN;
  • DELETE with JOIN;
  • avoiding duplicate-row mistakes;
  • indexing join columns;
  • composite index design;
  • included columns;
  • execution-plan considerations;
  • join algorithms;
  • cardinality and statistics;
  • performance troubleshooting.

Sample model

We will use a small commerce-style model.

SQL query: Sample modelT-SQL
CREATE TABLE dbo.Customers
(
    CustomerId       int           NOT NULL PRIMARY KEY,
    CustomerName     varchar(100)  NOT NULL,
    Status           varchar(20)   NOT NULL
);

CREATE TABLE dbo.Orders
(
    OrderId          bigint        NOT NULL PRIMARY KEY,
    CustomerId       int           NOT NULL,
    OrderDate        datetime2     NOT NULL,
    OrderStatus      varchar(20)   NOT NULL,
    TotalAmount      decimal(18,2) NOT NULL
);

CREATE TABLE dbo.OrderItems
(
    OrderItemId      bigint        NOT NULL PRIMARY KEY,
    OrderId          bigint        NOT NULL,
    ProductId        int           NOT NULL,
    Quantity         int           NOT NULL,
    UnitPrice        decimal(18,2) NOT NULL
);

CREATE TABLE dbo.Products
(
    ProductId        int           NOT NULL PRIMARY KEY,
    ProductName      varchar(150)  NOT NULL,
    CategoryId       int           NOT NULL,
    IsActive         bit           NOT NULL
);

CREATE TABLE dbo.Categories
(
    CategoryId       int           NOT NULL PRIMARY KEY,
    CategoryName     varchar(100)  NOT NULL
);
T-SQL38 linesRead-only query display. It is not connected to a database.

The relationships are:

Customers
   1
   │
   └──< Orders
          1
          │
          └──< OrderItems >── 1 Products >── 1 Categories

Basic INNER JOIN

An INNER JOIN returns rows where both sides match.

SQL query: Basic INNER JOINT-SQL
SELECT
    o.OrderId,
    o.OrderDate,
    c.CustomerName,
    o.TotalAmount
FROM dbo.Orders AS o
INNER JOIN dbo.Customers AS c
    ON c.CustomerId = o.CustomerId;
T-SQL8 linesRead-only query display. It is not connected to a database.

The join predicate is:

c.CustomerId = o.CustomerId

That predicate should usually compare columns with compatible data types.

Joining many tables

A multi-table JOIN is simply a chain of relationships.

SQL query: Joining many tablesT-SQL
SELECT
    o.OrderId,
    o.OrderDate,
    c.CustomerName,
    p.ProductName,
    cat.CategoryName,
    oi.Quantity,
    oi.UnitPrice,
    oi.Quantity * oi.UnitPrice AS LineAmount
FROM dbo.Orders AS o
INNER JOIN dbo.Customers AS c
    ON c.CustomerId = o.CustomerId
INNER JOIN dbo.OrderItems AS oi
    ON oi.OrderId = o.OrderId
INNER JOIN dbo.Products AS p
    ON p.ProductId = oi.ProductId
INNER JOIN dbo.Categories AS cat
    ON cat.CategoryId = p.CategoryId
WHERE o.OrderDate >= '2026-10-01'
  AND o.OrderDate <  '2026-11-01';
T-SQL20 linesRead-only query display. It is not connected to a database.

The important question is not:

How many tables can SQL Server join?

SQL Server can join many tables.

The better question is:

How many rows are flowing through each step of the plan?

Five well-indexed tables can be fast.

Three badly joined tables can be very slow.

Understand row multiplication

Suppose one order has five order items.

This query:

SQL query: Understand row multiplicationT-SQL
SELECT
    o.OrderId,
    c.CustomerName
FROM dbo.Orders AS o
INNER JOIN dbo.Customers AS c
    ON c.CustomerId = o.CustomerId
INNER JOIN dbo.OrderItems AS oi
    ON oi.OrderId = o.OrderId;
T-SQL8 linesRead-only query display. It is not connected to a database.

returns one row per order item, not one row per order.

If an order has five items, the order appears five times.

This is not a SQL Server bug.

It is the correct result for the join shape.

Before using DISTINCT, ask why the duplicates exist.

DISTINCT can hide a modeling or join problem while adding a sort or hash operation.

LEFT JOIN

Use LEFT JOIN when you need all rows from the left side even when there is no match.

SQL query: LEFT JOINT-SQL
SELECT
    c.CustomerId,
    c.CustomerName,
    o.OrderId
FROM dbo.Customers AS c
LEFT JOIN dbo.Orders AS o
    ON o.CustomerId = c.CustomerId;
T-SQL7 linesRead-only query display. It is not connected to a database.

Customers without orders still appear with NULL in the order columns.

A common LEFT JOIN mistake

This looks like a LEFT JOIN:

SQL query: A common LEFT JOIN mistakeT-SQL
SELECT
    c.CustomerId,
    c.CustomerName,
    o.OrderId
FROM dbo.Customers AS c
LEFT JOIN dbo.Orders AS o
    ON o.CustomerId = c.CustomerId
WHERE o.OrderStatus = 'Open';
T-SQL8 linesRead-only query display. It is not connected to a database.

But the WHERE predicate removes rows where o.OrderStatus is NULL.

That effectively turns the query into an INNER JOIN.

If you want customers even when they have no open orders, move the filter into the join:

SQL query: A common LEFT JOIN mistake (2)T-SQL
SELECT
    c.CustomerId,
    c.CustomerName,
    o.OrderId
FROM dbo.Customers AS c
LEFT JOIN dbo.Orders AS o
    ON o.CustomerId = c.CustomerId
   AND o.OrderStatus = 'Open';
T-SQL8 linesRead-only query display. It is not connected to a database.

Use EXISTS when you only need to know whether a match exists

Suppose you only need customers who have at least one order.

This works:

SQL query: Use EXISTS when you only need to know whether a match existsT-SQL
SELECT DISTINCT
    c.CustomerId,
    c.CustomerName
FROM dbo.Customers AS c
INNER JOIN dbo.Orders AS o
    ON o.CustomerId = c.CustomerId;
T-SQL6 linesRead-only query display. It is not connected to a database.

But EXISTS expresses the requirement more directly:

SQL query: Use EXISTS when you only need to know whether a match exists (2)T-SQL
SELECT
    c.CustomerId,
    c.CustomerName
FROM dbo.Customers AS c
WHERE EXISTS
(
    SELECT 1
    FROM dbo.Orders AS o
    WHERE o.CustomerId = c.CustomerId
);
T-SQL10 linesRead-only query display. It is not connected to a database.

When you do not need columns from the second table, EXISTS is often a cleaner design.

Joining aggregated data

Be careful when joining after aggregation.

Example:

SQL query: Joining aggregated dataT-SQL
WITH OrderTotals AS
(
    SELECT
        oi.OrderId,
        SUM(oi.Quantity * oi.UnitPrice) AS CalculatedTotal
    FROM dbo.OrderItems AS oi
    GROUP BY oi.OrderId
)
SELECT
    o.OrderId,
    o.TotalAmount,
    ot.CalculatedTotal
FROM dbo.Orders AS o
INNER JOIN OrderTotals AS ot
    ON ot.OrderId = o.OrderId;
T-SQL15 linesRead-only query display. It is not connected to a database.

Aggregating first can dramatically reduce the number of rows that participate in later joins.

That can improve both correctness and performance.

UPDATE using JOIN

SQL Server allows an UPDATE to target one table while using joined tables to identify or calculate values.

Example: deactivate products in a discontinued category.

SQL query: UPDATE using JOINT-SQL
UPDATE p
SET p.IsActive = 0
FROM dbo.Products AS p
INNER JOIN dbo.Categories AS c
    ON c.CategoryId = p.CategoryId
WHERE c.CategoryName = 'Discontinued';
T-SQL6 linesRead-only query display. It is not connected to a database.

The target is:

SQL query: UPDATE using JOIN (2)T-SQL
UPDATE p
T-SQL1 lineRead-only query display. It is not connected to a database.

The joined table is used to qualify the rows.

UPDATE using values from another table

Suppose you have a staging table:

SQL query: UPDATE using values from another tableT-SQL
CREATE TABLE dbo.ProductPriceStage
(
    ProductId int NOT NULL,
    NewPrice  decimal(18,2) NOT NULL
);
T-SQL5 linesRead-only query display. It is not connected to a database.

You can update the target using the stage values:

SQL query: UPDATE using values from another table (2)T-SQL
UPDATE p
SET p.CurrentPrice = s.NewPrice
FROM dbo.Products AS p
INNER JOIN dbo.ProductPriceStage AS s
    ON s.ProductId = p.ProductId;
T-SQL5 linesRead-only query display. It is not connected to a database.

The important requirement is that the join should identify each target row deterministically.

Dangerous UPDATE: multiple source rows match one target

Suppose the staging table contains multiple rows for one ProductId.

Then:

SQL query: Dangerous UPDATE: multiple source rows match one targetT-SQL
UPDATE p
SET p.CurrentPrice = s.NewPrice
FROM dbo.Products AS p
INNER JOIN dbo.ProductPriceStage AS s
    ON s.ProductId = p.ProductId;
T-SQL5 linesRead-only query display. It is not connected to a database.

is ambiguous from a business perspective.

You should first guarantee uniqueness:

SQL query: Dangerous UPDATE: multiple source rows match one target (2)T-SQL
SELECT
    ProductId,
    COUNT(*) AS RowCount
FROM dbo.ProductPriceStage
GROUP BY ProductId
HAVING COUNT(*) > 1;
T-SQL6 linesRead-only query display. It is not connected to a database.

A unique constraint or unique index is even better when the model requires one row per product.

SQL query: Dangerous UPDATE: multiple source rows match one target (3)T-SQL
CREATE UNIQUE INDEX UX_ProductPriceStage_ProductId
ON dbo.ProductPriceStage(ProductId);
T-SQL2 linesRead-only query display. It is not connected to a database.

UPDATE multiple tables?

A single SQL Server UPDATE statement updates one target table.

You can join many tables to decide which rows and values should be updated, but only one table is the target.

If multiple tables need changes, use separate statements inside a transaction when atomicity is required.

BEGIN TRANSACTION;

UPDATE ...;

UPDATE ...;

COMMIT TRANSACTION;

DELETE using JOIN

SQL Server supports deleting rows from one table while using joined tables to identify them.

Example: delete order items for cancelled orders older than a cutoff.

SQL query: DELETE using JOINT-SQL
DELETE oi
FROM dbo.OrderItems AS oi
INNER JOIN dbo.Orders AS o
    ON o.OrderId = oi.OrderId
WHERE o.OrderStatus = 'Cancelled'
  AND o.OrderDate < '2025-01-01';
T-SQL6 linesRead-only query display. It is not connected to a database.

The target table is:

SQL query: DELETE using JOIN (2)T-SQL
DELETE oi
T-SQL1 lineRead-only query display. It is not connected to a database.

This is an important SQL Server syntax detail.

Safer DELETE workflow

Before running the DELETE, run the same logic as a SELECT.

SQL query: Safer DELETE workflowT-SQL
SELECT
    oi.*
FROM dbo.OrderItems AS oi
INNER JOIN dbo.Orders AS o
    ON o.OrderId = oi.OrderId
WHERE o.OrderStatus = 'Cancelled'
  AND o.OrderDate < '2025-01-01';
T-SQL7 linesRead-only query display. It is not connected to a database.

Then validate the row count.

SQL query: Safer DELETE workflow (2)T-SQL
SELECT
    COUNT_BIG(*) AS RowsToDelete
FROM dbo.OrderItems AS oi
INNER JOIN dbo.Orders AS o
    ON o.OrderId = oi.OrderId
WHERE o.OrderStatus = 'Cancelled'
  AND o.OrderDate < '2025-01-01';
T-SQL7 linesRead-only query display. It is not connected to a database.

Only then convert it to the DELETE.

For large deletes, batching may be safer.

DELETE in batches

Deleting millions of rows in one transaction can create:

  • transaction log growth;
  • blocking;
  • lock escalation;
  • long rollback times;
  • replication or AG pressure.

A batch pattern can help:

SQL query: DELETE in batchesT-SQL
WHILE 1 = 1
BEGIN
    DELETE TOP (10000) oi
    FROM dbo.OrderItems AS oi
    INNER JOIN dbo.Orders AS o
        ON o.OrderId = oi.OrderId
    WHERE o.OrderStatus = 'Cancelled'
      AND o.OrderDate < '2025-01-01';

    IF @@ROWCOUNT = 0
        BREAK;
END;
T-SQL12 linesRead-only query display. It is not connected to a database.

The right batch size depends on the workload.

DELETE with EXISTS

Sometimes EXISTS is simpler:

SQL query: DELETE with EXISTST-SQL
DELETE oi
FROM dbo.OrderItems AS oi
WHERE EXISTS
(
    SELECT 1
    FROM dbo.Orders AS o
    WHERE o.OrderId = oi.OrderId
      AND o.OrderStatus = 'Cancelled'
      AND o.OrderDate < '2025-01-01'
);
T-SQL10 linesRead-only query display. It is not connected to a database.

Both approaches can produce good plans.

Choose the one that makes the intent clearest and validate the execution plan.

Indexes are critical for JOIN performance

A JOIN commonly benefits from indexes on:

  • primary keys;
  • foreign keys;
  • filtering columns;
  • frequently joined business keys.

In our example:

Customers.CustomerId
Orders.CustomerId
OrderItems.OrderId
OrderItems.ProductId
Products.ProductId
Products.CategoryId
Categories.CategoryId

Primary keys are already indexed.

Foreign keys are not automatically indexed in SQL Server.

That is important.

Index foreign-key columns when the access pattern needs them

For example:

SQL query: Index foreign-key columns when the access pattern needs themT-SQL
CREATE INDEX IX_Orders_CustomerId
ON dbo.Orders(CustomerId);
T-SQL2 linesRead-only query display. It is not connected to a database.

and:

SQL query: Index foreign-key columns when the access pattern needs them (2)T-SQL
CREATE INDEX IX_OrderItems_OrderId
ON dbo.OrderItems(OrderId);
T-SQL2 linesRead-only query display. It is not connected to a database.

These indexes can help SQL Server efficiently find child rows during joins.

They can also improve parent-row updates or deletes where referential checks need to inspect child tables.

But do not blindly index every foreign key.

Measure the workload.

Composite indexes for JOIN + filter

Suppose the query is:

SQL query: Composite indexes for JOIN + filterT-SQL
SELECT
    o.OrderId,
    o.OrderDate,
    o.TotalAmount
FROM dbo.Orders AS o
INNER JOIN dbo.Customers AS c
    ON c.CustomerId = o.CustomerId
WHERE o.CustomerId = @CustomerId
  AND o.OrderDate >= @StartDate
  AND o.OrderDate < @EndDate;
T-SQL10 linesRead-only query display. It is not connected to a database.

A useful index may be:

SQL query: Composite indexes for JOIN + filter (2)T-SQL
CREATE INDEX IX_Orders_CustomerId_OrderDate
ON dbo.Orders(CustomerId, OrderDate)
INCLUDE (OrderId, TotalAmount);
T-SQL3 linesRead-only query display. It is not connected to a database.

Why?

The key columns support:

CustomerId equality
+
OrderDate range

The included columns can cover the SELECT output.

Column order matters

These are not equivalent:

(CustomerId, OrderDate)

and:

(OrderDate, CustomerId)

If the common predicate is:

CustomerId = constant
AND OrderDate BETWEEN ...

then (CustomerId, OrderDate) is often a better access pattern.

But index design depends on real workload distribution and query patterns.

Covering indexes

A covering index contains enough columns to satisfy the query without additional lookups.

Example:

SQL query: Covering indexesT-SQL
CREATE INDEX IX_OrderItems_OrderId
ON dbo.OrderItems(OrderId)
INCLUDE (ProductId, Quantity, UnitPrice);
T-SQL3 linesRead-only query display. It is not connected to a database.

Then a query filtering or joining by OrderId may avoid repeated key lookups.

Covering can reduce reads.

But every included column increases index size and write cost.

Indexes accelerate reads but must be maintained during:

INSERT
UPDATE
DELETE

If a table has 20 overlapping indexes, a JOIN-based update may spend significant time maintaining indexes.

Performance tuning is a balance:

read benefit
vs
write cost
vs
storage
vs
maintenance

Implicit conversions can destroy a good JOIN

Suppose:

TableA.CustomerId = int
TableB.CustomerId = varchar(20)

Then a join like:

ON a.CustomerId = b.CustomerId

requires a conversion.

That can:

  • prevent efficient seeks;
  • increase CPU;
  • distort estimates;
  • increase reads.

Join columns should use compatible data types whenever possible.

The best fix is usually the data model, not wrapping both columns in functions.

Avoid functions on JOIN columns

This is often problematic:

ON CAST(a.CustomerId AS varchar(20)) = b.CustomerId

or:

ON UPPER(a.Code) = UPPER(b.Code)

These expressions can make index access more difficult.

Prefer storing comparable values in comparable types and normalized formats.

Many-to-many joins can explode row counts

Suppose both sides contain duplicate keys:

Table A:
Key = 10 appears 100 times

Table B:
Key = 10 appears 200 times

Joining on Key can produce:

100 × 200 = 20,000 rows

for that key alone.

This is one reason join queries can unexpectedly become huge.

Before joining, understand the expected relationship:

one-to-one
one-to-many
many-to-one
many-to-many

If you expect one-to-one, enforce it with a unique constraint or index where appropriate.

Statistics influence JOIN choices

SQL Server chooses plans based on estimated row counts.

Those estimates influence whether it chooses:

  • Nested Loops;
  • Hash Match;
  • Merge Join;
  • join order;
  • memory grant;
  • parallelism.

If statistics are inaccurate, the join strategy can be wrong.

Nested Loops

Nested Loops often work well when:

  • the outer input is small;
  • the inner side has an efficient index;
  • only a limited number of rows are expected.

Conceptually:

For each outer row
    seek matching inner rows

This can be extremely fast.

It can also be disastrous if SQL Server expects 10 rows and actually processes 10 million.

Hash Match

Hash joins are common for larger sets.

Conceptually:

build hash table from one input
probe it with the other input

They can work well for large joins even without useful ordering.

But they may require significant memory.

Poor estimates can cause spills to tempdb.

Merge Join

Merge Join can be very efficient when both inputs are already sorted on the join keys.

Conceptually:

sorted input A
+
sorted input B
→ walk both in order

But obtaining the required order may itself be expensive if SQL Server needs a sort.

Do not force a join type without evidence

SQL Server supports join hints, but they should be exceptional.

For example:

OPTION (HASH JOIN);

may improve one test and hurt different parameter values or future data distributions.

Prefer fixing the underlying problem:

  • index;
  • statistics;
  • data type;
  • cardinality;
  • query shape.

Filter early, but understand SQL Server optimization

This does not mean manually wrapping every table in a CTE just to “filter first.”

SQL Server’s optimizer can reorder joins and push predicates.

What matters is giving it:

  • SARGable predicates;
  • useful indexes;
  • good statistics;
  • clear relationships.

A good predicate:

o.OrderDate >= @StartDate
AND o.OrderDate < @EndDate

A less useful predicate:

YEAR(o.OrderDate) = 2026

The first form is more likely to support an index seek on OrderDate.

Avoid SELECT *

In large joins, SELECT * can:

  • return unnecessary data;
  • increase network traffic;
  • increase memory requirements;
  • prevent narrow covering indexes;
  • make code fragile when schemas change.

Select only the columns you need.

Join only what you need

This query:

SQL query: Join only what you needT-SQL
SELECT
    o.OrderId,
    c.CustomerName
FROM dbo.Orders AS o
INNER JOIN dbo.Customers AS c
    ON c.CustomerId = o.CustomerId
INNER JOIN dbo.OrderItems AS oi
    ON oi.OrderId = o.OrderId
INNER JOIN dbo.Products AS p
    ON p.ProductId = oi.ProductId;
T-SQL10 linesRead-only query display. It is not connected to a database.

should not join OrderItems and Products if no filter or output requires them.

Every unnecessary join increases complexity and can increase work.

CTEs do not automatically improve performance

A CTE can improve readability:

WITH RecentOrders AS
(
    SELECT
        OrderId,
        CustomerId,
        OrderDate
    FROM dbo.Orders
    WHERE OrderDate >= @StartDate
)
SELECT ...

But a normal CTE is not automatically materialized.

SQL Server can inline it into the larger plan.

Use CTEs for clarity, not as a guaranteed optimization barrier.

Temporary tables can help large multi-stage joins

Sometimes breaking a complex query into stages improves:

  • cardinality estimates;
  • indexing opportunities;
  • troubleshooting;
  • reuse.

Example:

SQL query: Temporary tables can help large multi-stage joinsT-SQL
SELECT
    o.OrderId,
    o.CustomerId
INTO #RecentOrders
FROM dbo.Orders AS o
WHERE o.OrderDate >= @StartDate
  AND o.OrderDate < @EndDate;

CREATE INDEX IX_RecentOrders_CustomerId
ON #RecentOrders(CustomerId);
T-SQL10 linesRead-only query display. It is not connected to a database.

Then join #RecentOrders to the rest of the model.

This can be useful when an intermediate result is much smaller than the original data.

But temporary tables also add tempdb work.

Measure the tradeoff.

UPDATE and DELETE need the same performance thinking as SELECT

A common mistake is tuning only SELECT queries.

This UPDATE:

SQL query: UPDATE and DELETE need the same performance thinking as SELECTT-SQL
UPDATE o
SET o.OrderStatus = 'Archived'
FROM dbo.Orders AS o
INNER JOIN dbo.Customers AS c
    ON c.CustomerId = o.CustomerId
WHERE c.Status = 'Inactive';
T-SQL6 linesRead-only query display. It is not connected to a database.

still needs SQL Server to:

  1. find qualifying customers;
  2. find matching orders;
  3. update rows;
  4. maintain indexes;
  5. log changes;
  6. hold locks.

The join and index design matter just as much.

Large updates can cause blocking

A single UPDATE affecting millions of rows can:

  • hold locks for a long time;
  • escalate locks;
  • grow the transaction log;
  • block readers/writers;
  • create long rollbacks.

Batching can help when business rules allow it.

SQL query: Large updates can cause blockingT-SQL
WHILE 1 = 1
BEGIN
    UPDATE TOP (5000) o
    SET o.OrderStatus = 'Archived'
    FROM dbo.Orders AS o
    INNER JOIN dbo.Customers AS c
        ON c.CustomerId = o.CustomerId
    WHERE c.Status = 'Inactive'
      AND o.OrderStatus <> 'Archived';

    IF @@ROWCOUNT = 0
        BREAK;
END;
T-SQL13 linesRead-only query display. It is not connected to a database.

Check the execution plan

For important joins, inspect the actual execution plan.

Look for:

  • estimated vs actual rows;
  • scans on large tables;
  • repeated key lookups;
  • hash spills;
  • sort spills;
  • implicit conversions;
  • warnings;
  • expensive many-to-many behavior;
  • unexpected join algorithms.

Do not focus only on the operator cost percentage.

The percentages are optimizer estimates, not a direct measurement of runtime.

Measure logical reads

SET STATISTICS IO ON is extremely useful.

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT ...

Compare before and after.

A query improving from:

8,000,000 logical reads

to:

80,000 logical reads

usually represents a meaningful reduction in work.

Example: poorly indexed multi-table JOIN

Imagine this query:

SQL query: Example: poorly indexed multi-table JOINT-SQL
SELECT
    c.CustomerName,
    o.OrderId,
    p.ProductName,
    oi.Quantity
FROM dbo.Customers AS c
INNER JOIN dbo.Orders AS o
    ON o.CustomerId = c.CustomerId
INNER JOIN dbo.OrderItems AS oi
    ON oi.OrderId = o.OrderId
INNER JOIN dbo.Products AS p
    ON p.ProductId = oi.ProductId
WHERE c.Status = 'Active'
  AND o.OrderDate >= '2026-10-01';
T-SQL14 linesRead-only query display. It is not connected to a database.

Potential supporting indexes:

SQL query: Example: poorly indexed multi-table JOIN (2)T-SQL
CREATE INDEX IX_Customers_Status
ON dbo.Customers(Status)
INCLUDE (CustomerId, CustomerName);

CREATE INDEX IX_Orders_CustomerId_OrderDate
ON dbo.Orders(CustomerId, OrderDate)
INCLUDE (OrderId);

CREATE INDEX IX_OrderItems_OrderId
ON dbo.OrderItems(OrderId)
INCLUDE (ProductId, Quantity);
T-SQL11 linesRead-only query display. It is not connected to a database.

The primary key on Products(ProductId) already supports the final join.

These indexes are examples, not universal prescriptions.

Test them against real workload.

A better index may depend on the most selective predicate

Suppose nearly every customer is active.

Then:

Customers(Status)

may not be selective enough to help.

An index that looks logical on paper may still not be useful.

Index design should consider:

selectivity
row counts
query frequency
write volume
existing indexes
actual execution plans

Foreign-key indexes can also help DELETE performance

Suppose you delete a customer.

SQL Server may need to verify whether child rows exist in Orders.

If Orders.CustomerId is not indexed, referential checks can require a scan.

An index on the foreign key can make those checks much cheaper.

This is one reason foreign-key indexing can matter even beyond SELECT performance.

Good JOIN design checklist

Before approving a production join, ask:

Correctness

  • Is the expected relationship one-to-one, one-to-many, or many-to-many?
  • Can duplicate matches multiply rows?
  • Does LEFT JOIN remain a LEFT JOIN after WHERE predicates?
  • Is the UPDATE/DELETE target deterministic?

Data types

  • Are join columns the same or compatible types?
  • Are there implicit conversions?
  • Are functions being applied to join columns?

Indexing

  • Are primary keys indexed?
  • Do frequently used foreign keys need indexes?
  • Does the query need a composite index?
  • Would INCLUDE columns avoid expensive lookups?
  • Are there overlapping indexes already?

Filtering

  • Are predicates SARGable?
  • Can the query reduce rows efficiently?
  • Are unnecessary joins present?

Plan quality

  • Estimated vs actual rows?
  • Which join algorithm?
  • Any scan on a very large table?
  • Spills?
  • Warnings?
  • Repeated lookups?

Write impact

For UPDATE/DELETE:

  • How many rows?
  • How much logging?
  • Blocking risk?
  • Batch required?
  • How many indexes need maintenance?

Final thoughts

JOIN performance is not determined by the number of tables alone.

It depends on:

relationships
row counts
cardinality
indexes
statistics
data types
filters
join predicates
selected columns
write volume

A well-designed five-table join can finish in milliseconds.

A badly designed two-table join can scan hundreds of millions of rows.

The practical rules are simple:

  • understand the relationship before writing the JOIN;
  • do not use DISTINCT to hide duplicate logic;
  • use EXISTS when you only need existence;
  • validate UPDATE and DELETE joins with SELECT first;
  • index join columns when the workload benefits;
  • design composite indexes around real predicates;
  • avoid implicit conversions;
  • inspect actual plans;
  • compare logical reads;
  • measure before and after.

The goal is not just to make the SQL syntactically correct.

The goal is to make the relationship clear, deterministic, and efficient.

Tags

  • SQL Server
  • Performance
  • Indexing
  • JOINs
  • Execution Plans