INNER, OUTER, CROSS, and Self Joins with Cardinality Reasoning

Learning outcomes

A join is not merely syntax for “put two tables together.” It defines which row combinations are eligible to exist in the result. If you cannot predict the cardinality before running a small join, a plausible-looking output can hide missing rows, duplicate multiplication, or a silently converted outer join. This lesson treats row-count reasoning as a correctness tool.

Start with relationships and expected multiplicity

Each work order references exactly one customer, so joining all seven work orders to customers should still produce seven rows. A work order may have no technician, so an inner join to technicians should drop the unassigned row 1003, while a left join should preserve it with NULL-extended technician columns. The tag table is one-to-many: work order 1001 has two tags and work order 1004 has two tags, so joining work orders to tags can legitimately multiply rows.

USE servicehub_query_lab;SELECT COUNT(*) AS wo_customer_rowsFROM work_orders AS wJOIN customers AS c ON c.customer_id = w.customer_id;SELECT COUNT(*) AS inner_tech_rowsFROM work_orders AS wJOIN technicians AS t ON t.technician_id = w.technician_id;SELECT COUNT(*) AS left_tech_rowsFROM work_orders AS wLEFT JOIN technicians AS t ON t.technician_id = w.technician_id;SELECT COUNT(*) AS wo_tag_rowsFROM work_orders AS wJOIN work_order_tags AS wt ON wt.work_order_id = w.work_order_id;

INNER JOIN: only matched combinations survive

An inner join returns row combinations that satisfy its join condition. MySQL may choose a nested-loop, hash join, or another optimizer strategy depending on indexes and estimates, but those physical choices must preserve the same inner-join result.

SELECT w.work_order_id,       c.customer_name,       t.technician_name,       w.statusFROM work_orders AS wJOIN customers AS c  ON c.customer_id = w.customer_idJOIN technicians AS t  ON t.technician_id = w.technician_idWHERE w.status = 'open'ORDER BY w.work_order_id;

Work order 1003 is absent because its technician_id is NULL and no technician row can satisfy equality with it. If the business question is “show open orders that already have an assigned technician,” this is correct. If the question is “show every open order and display technician information when available,” the join type is wrong.

LEFT JOIN and the ON-versus-WHERE trap

A left outer join preserves every row from its left input. When no right-side row matches, MySQL produces an output row whose right-side columns are NULL. This NULL extension is exactly why a filter on the right table can change the meaning when it is moved from ON to WHERE.

SELECT w.work_order_id,       t.technician_nameFROM work_orders AS wLEFT JOIN technicians AS t  ON t.technician_id = w.technician_idWHERE w.status='open'ORDER BY w.work_order_id;

-- Wrong if the requirement is "keep unassigned work orders too".SELECT w.work_order_id, t.technician_nameFROM work_orders AS wLEFT JOIN technicians AS t  ON t.technician_id = w.technician_idWHERE w.status='open'  AND t.active = TRUE;-- The right-side condition participates in matching instead.SELECT w.work_order_id, t.technician_nameFROM work_orders AS wLEFT JOIN technicians AS t  ON t.technician_id = w.technician_id AND t.active = TRUEWHERE w.status='open'ORDER BY w.work_order_id;

In the first query, the NULL-extended unassigned row fails t.active = TRUE in the WHERE clause and disappears. In the second, work orders are still preserved; an inactive or missing technician simply produces NULL right-side columns. Neither query is universally “better.” They answer different questions.

MySQL also supports RIGHT JOIN/RIGHT OUTER JOIN. The optimizer can rewrite right outer joins as left outer joins, and many teams standardize on left joins simply for readability. MySQL 8.4 does not provide a native FULL OUTER JOIN operator; when a genuine full-outer result is required, compose it deliberately from supported operations and test duplicate semantics rather than assuming syntax from another database product.

CROSS JOIN and accidental Cartesian products

A Cartesian product pairs every row from one input with every row from another. It can be intentional—for example, generating every technician/day combination for a planning matrix—but it is often an accident caused by a missing join predicate. With 7 work orders and 5 technicians, the product has 35 rows. At production scale, accidental multiplication can explode memory, temporary-table use, and response size.

SELECT COUNT(*) AS product_rowsFROM work_orders AS wCROSS JOIN technicians AS t;-- Expected: 35-- Keep deliberate CROSS JOINs narrow and obvious.SELECT w.work_order_id, t.technician_nameFROM work_orders AS wCROSS JOIN technicians AS tWHERE w.work_order_id IN (1001,1003)  AND t.technician_id IN (11,12)ORDER BY w.work_order_id, t.technician_id;-- Expected: 4 combinations

One-to-many joins multiply rows legitimately

The tag relation demonstrates why “one work order equals one output row” is not always true. Joining a parent to a child table returns one row per matching child unless you aggregate or otherwise reduce the child side.

SELECT w.work_order_id, w.summary, wt.tagFROM work_orders AS wJOIN work_order_tags AS wt  ON wt.work_order_id = w.work_order_idWHERE w.work_order_id IN (1001,1004)ORDER BY w.work_order_id, wt.tag;SELECT w.work_order_id, COUNT(*) AS tag_countFROM work_orders AS wJOIN work_order_tags AS wt  ON wt.work_order_id = w.work_order_idGROUP BY w.work_order_idORDER BY w.work_order_id;

When a report later joins several independent one-to-many relations, row multiplication can compound. Predict that shape before adding aggregates such as SUM(), otherwise totals can be multiplied too.

Self joins: one table playing two roles

The technicians table contains a supervisor relationship back to itself. A self join uses aliases so the same physical table can represent the employee role and the supervisor role.

SELECT e.technician_id,       e.technician_name AS employee,       s.technician_name AS supervisorFROM technicians AS eLEFT JOIN technicians AS s  ON s.technician_id = e.supervisor_idORDER BY e.technician_id;

Mina has no supervisor, so the left join preserves her with a NULL supervisor name. This same adjacency-list structure becomes the recursive CTE hierarchy in Lesson 4.

Observe optimizer evidence, but keep cardinality logic separate

EXPLAIN FORMAT=TREESELECT w.work_order_id, c.customer_nameFROM work_orders AS wJOIN customers AS c  ON c.customer_id = w.customer_idWHERE w.status='open';SELECT COUNT(*) AS result_rowsFROM work_orders AS wJOIN customers AS c  ON c.customer_id = w.customer_idWHERE w.status='open';

The plan may show indexed lookups, table scans, or hash joins depending on the current schema and optimizer. That does not replace the semantic reasoning: every qualifying work order has exactly one customer, so the join should not multiply those rows.

EXPLAIN ANALYZESELECT w.work_order_id, c.customer_nameFROM work_orders AS wJOIN customers AS c  ON c.customer_id = w.customer_idWHERE w.status='open';

EXPLAIN ANALYZE executes the statement and reports observed iterator rows/timing in addition to estimates. Do not copy a fixed timing into documentation as a universal benchmark: record the learner’s own output, compare estimated versus actual row counts, and investigate large estimation errors later in the optimizer chapters.

Hands-on lab: predict before execute

1. Write down the expected row count before each of these: work-orders/customers inner join, technicians inner join, technicians left join, tags inner join, and the full work-orders × technicians product.
2. Execute each count and reconcile any mismatch before continuing.
3. Demonstrate how WHERE t.active=TRUE after a left join removes the unassigned row.
4. Move the same condition into ON and explain the changed business meaning.
5. Join work orders 1001 and 1004 to tags and explain why each appears twice.
6. Run the self join and identify the root technician whose supervisor is NULL.

Production judgment and next bridge

For production joins, review keys and relationship multiplicities together with the query. Track estimated versus observed row counts when performance becomes important, but first verify the business cardinality. A fast wrong join is still wrong. Be especially cautious when outer joins, optional relationships, and multiple child tables meet in one report.

The next lesson asks whether a relationship is better expressed as a nested query: scalar lookup, existence test, membership test, correlated calculation, or derived table. MySQL’s optimizer may transform some of those forms, but their SQL semantics must be understood before comparing plans.

Authoritative references

• MySQL 8.4 Reference Manual — JOIN Clause
• MySQL 8.4 Reference Manual — Nested Join Optimization
• MySQL 8.4 Reference Manual — Outer Join Optimization
• MySQL 8.4 Reference Manual — Hash Join Optimization
• MySQL 8.4 Reference Manual — Condition Filtering