INNER/OUTER/CROSS Joins, Cardinality, Join Predicates, and Duplicate Multiplication

Learning outcomes

A ServiceHub dashboard joins work orders to technicians, visits, and tags. The developer expects four work orders but sees twelve rows and concludes that SQL Server “duplicated” data. Nothing was duplicated by the engine: the join predicate correctly formed combinations implied by one-to-many relationships. This lesson builds a row-count mental model before discussing join algorithms or indexes.

1. Cardinality begins with the relationship, not JOIN syntax

An INNER JOIN returns combinations for which its ON predicate is TRUE. If one work order has two visits, joining WorkOrder to Visit can legitimately return two rows for that work order. Add three tags and a naive join across both one-to-many relationships can yield six combinations for that work order. The repeated work-order columns are not evidence of corruption; they reflect the grain of the joined result.

USE ServiceHubLab;GOIF SCHEMA_ID(N'lab05') IS NULL EXEC(N'CREATE SCHEMA lab05 AUTHORIZATION dbo;');GODROP TABLE IF EXISTS lab05.WorkOrderTag;DROP TABLE IF EXISTS lab05.WorkOrderVisit;GOCREATE TABLE lab05.WorkOrderVisit(    visit_id int IDENTITY(1,1) NOT NULL CONSTRAINT PK_lab05_Visit PRIMARY KEY,    work_order_id bigint NOT NULL,    visit_started_at datetime2(0) NOT NULL,    outcome varchar(20) NOT NULL);CREATE TABLE lab05.WorkOrderTag(    work_order_id bigint NOT NULL,    tag varchar(20) NOT NULL,    CONSTRAINT PK_lab05_Tag PRIMARY KEY(work_order_id, tag));GOINSERT lab05.WorkOrderVisit(work_order_id,visit_started_at,outcome) VALUES(1001,'2026-08-10T10:00:00','inspection'),(1001,'2026-08-10T11:30:00','follow-up'),(1002,'2026-08-10T11:00:00','replacement'),(1004,'2026-08-11T09:30:00','calibration');INSERT lab05.WorkOrderTag(work_order_id,tag) VALUES(1001,'pump'),(1001,'urgent'),(1001,'north'),(1002,'sensor'),(1004,'meter');GO

The tables deliberately omit foreign keys so the lesson can focus on query grain; Chapter 04 already taught integrity design. In production, model the relationship explicitly and index foreign-key/access columns when workload evidence supports it.

2. Predict fan-out before running the join

For work order 1001, Visit has 2 rows and Tag has 3 rows. Joining both independently by work_order_id produces 2 × 3 = 6 combinations for that order. If the report grain is “one row per work order,” the query is wrong even if every JOIN is syntactically valid.

USE ServiceHubLab;GOSELECT w.work_order_id,       v.visit_id,       t.tagFROM ops.WorkOrder AS wJOIN lab05.WorkOrderVisit AS v  ON v.work_order_id = w.work_order_idJOIN lab05.WorkOrderTag AS t  ON t.work_order_id = w.work_order_idWHERE w.work_order_id = 1001ORDER BY v.visit_id, t.tag;GOSELECT COUNT(*) AS joined_rowsFROM lab05.WorkOrderVisit AS vJOIN lab05.WorkOrderTag AS t  ON t.work_order_id = v.work_order_idWHERE v.work_order_id = 1001;GO

Expected count: 6. Adding DISTINCT w.work_order_id could hide the multiplication in the final projection, but it does not repair the logical mistake if you also need visit or tag facts. First choose the target grain: aggregate tags separately, choose one visit, or return a detail result whose multiplicity is intentional.

3. LEFT JOIN preserves the left side through null-extension

A LEFT OUTER JOIN keeps each left row even if there is no matching right row. For an unmatched row, columns from the right input are null-extended. A predicate in the ON clause participates in deciding which right rows match. A predicate on right-side columns in the WHERE clause runs after the join and can reject those NULL-extended rows, effectively turning the query into inner-like behavior for that condition.

USE ServiceHubLab;GO-- Keep every work order; match only follow-up visits.SELECT w.work_order_id, v.visit_id, v.outcomeFROM ops.WorkOrder AS wLEFT JOIN lab05.WorkOrderVisit AS v  ON v.work_order_id = w.work_order_id AND v.outcome = 'follow-up'ORDER BY w.work_order_id, v.visit_id;GO-- Different meaning: NULL-extended rows fail this WHERE predicate.SELECT w.work_order_id, v.visit_id, v.outcomeFROM ops.WorkOrder AS wLEFT JOIN lab05.WorkOrderVisit AS v  ON v.work_order_id = w.work_order_idWHERE v.outcome = 'follow-up'ORDER BY w.work_order_id, v.visit_id;GO

The first query answers “all work orders, plus a follow-up visit when one exists.” The second answers “rows whose joined visit is a follow-up,” excluding work orders with no such match. Moving predicates between ON and WHERE is therefore a semantic change for outer joins, not merely a formatting preference.

4. CROSS JOIN is explicit Cartesian product

A CROSS JOIN returns every combination of rows from its two inputs. This is useful for generating matrices—such as every technician × every target status—but dangerous when accidental. Old comma-separated FROM syntax can also create a Cartesian product if a predicate is missing; prefer explicit JOIN syntax so code review can see the intended relationship.

USE ServiceHubLab;GOWITH TargetStatus AS(    SELECT 'new' AS status_name    UNION ALL SELECT 'assigned'    UNION ALL SELECT 'onsite')SELECT t.technician_code, s.status_nameFROM ops.Technician AS tCROSS JOIN TargetStatus AS sWHERE t.is_active = 1ORDER BY t.technician_code, s.status_name;GO-- Do not run this pattern against large tables without understanding the product:SELECT COUNT_BIG(*) AS cartesian_rowsFROM ops.WorkOrder AS wCROSS JOIN lab05.WorkOrderTag AS t;GO

The count is the product of input row counts. A cross product can be legitimate, but the query should make that business intention obvious and bound the inputs before scale makes the result impractical.

5. Estimates can expose a modeling/query problem

The optimizer estimates join cardinality from statistics, constraints, predicates, and its cardinality-estimation model. A poor estimate can be caused by stale/insufficient statistics or correlations, but also by a query whose join predicates do not represent the real relationship. Do not respond to every estimate mismatch with “add an index.” First compare actual row counts to the grain you intended.

USE ServiceHubLab;GOSET STATISTICS IO ON;SELECT w.work_order_id,       COUNT(DISTINCT v.visit_id) AS visit_count,       COUNT(DISTINCT t.tag) AS tag_count,       COUNT_BIG(*) AS joined_combinationsFROM ops.WorkOrder AS wLEFT JOIN lab05.WorkOrderVisit AS v ON v.work_order_id = w.work_order_idLEFT JOIN lab05.WorkOrderTag AS t ON t.work_order_id = w.work_order_idGROUP BY w.work_order_idORDER BY w.work_order_id;SET STATISTICS IO OFF;GO

Inspect the actual execution plan in your supported tool if available. Compare estimated versus actual rows at joins, but interpret the mismatch alongside keys and relationship cardinality. Chapter 10 will go deeply into cardinality estimation; here the objective is to prove the row semantics first.

6. Failure analysis and production judgment

Logical join types are edition-neutral and available on free Developer/Express. Physical algorithms are optimizer choices and can vary with data, statistics, memory, indexes, compatibility level, and parallelism. Do not force a join algorithm as a first-line repair for a semantic row-count error. Monitor unexpected row growth, estimates versus actuals, memory grants/spills later in the course, and application assumptions about one-row-per-entity.

Authoritative references

• FROM, JOIN and APPLY — join and APPLY semantics
• SELECT — logical query composition
• Execution plans — estimated and actual physical strategies
• SQL Server 2025 build versions — servicing baseline