Join Types, Derived Tables, Correlated Subqueries, EXISTS/IN, and Semijoin Reasoning

Learning outcomes

ServiceHub now needs questions that cross tables: list every technician and any open work assigned to them; find technicians who have at least one expensive work order; find technicians with no work at all; summarize recent event hours by work order. The SQL can be written with joins, subqueries or derived tables, but superficially similar forms do not always mean the same thing—especially when NULL and outer joins are involved.

The key mental model is cardinality. An inner join combines matching row pairs. An outer join additionally preserves unmatched rows from one side by introducing NULL-extended columns. A semijoin asks whether at least one match exists and returns an outer row once, regardless of how many inner matches exist. A correlated subquery is evaluated in a logical context that references an outer row, though the optimizer may transform it into a different physical strategy.

1. Start with the number of rows you intend to return

Before writing a join, state the grain of the desired result. “One row per work order” is different from “one row per technician” or “one row per technician-event pair.” Many accidental duplicates are not database bugs; they are the mathematically correct result of joining one-to-many relationships without deciding which grain the report should have.

SELECT t.technician_id, t.display_name,       w.work_order_id, w.statusFROM technicians AS tJOIN work_orders AS w  ON w.technician_id = t.technician_idORDER BY t.technician_id, w.work_order_id;

If Ava owns two work orders, Ava appears twice because the result grain is a technician-work-order pair. Adding DISTINCT without understanding that cardinality often hides a modeling/query mistake and can add unnecessary duplicate-elimination work. Choose DISTINCT only when set semantics are truly the requirement.

2. INNER, LEFT and CROSS joins answer different questions

INNER JOIN returns only matching row pairs. LEFT JOIN preserves every row from the left side even if no right-side match exists, filling right-side columns with NULL for unmatched rows. CROSS JOIN deliberately forms combinations and therefore multiplies cardinalities. Cross joins are useful for generating grids or test combinations, but an accidentally missing join predicate can explode the result.

SELECT t.technician_id, t.display_name, w.work_order_idFROM technicians AS tLEFT JOIN work_orders AS w  ON w.technician_id = t.technician_idORDER BY t.technician_id, w.work_order_id;

3. The classic outer-join bug: filtering the right side in WHERE

Suppose the requirement is “show every technician and any currently open work assigned to them.” A common rewrite places w.status='open' in WHERE after a LEFT JOIN. That predicate is evaluated after unmatched technicians have been represented with NULL right-side columns. NULL = 'open' is UNKNOWN, so those rows are removed. The query has silently become equivalent to an inner-match requirement for this condition.

-- WRONG for “every technician, plus open work if present”SELECT t.technician_id, t.display_name, w.work_order_id, w.statusFROM technicians AS tLEFT JOIN work_orders AS w  ON w.technician_id = t.technician_idWHERE w.status = 'open'ORDER BY t.technician_id, w.work_order_id;

SELECT t.technician_id, t.display_name, w.work_order_id, w.statusFROM technicians AS tLEFT JOIN work_orders AS w  ON w.technician_id = t.technician_id AND w.status = 'open'ORDER BY t.technician_id, w.work_order_id;

The second query preserves left rows and limits which right rows qualify as matches. This does not mean every right-side filter belongs in ON. Predicate placement follows the requirement: decide whether a condition defines a match or filters the final result.

4. EXISTS expresses existence without multiplying outer rows

When the requirement is “which technicians have at least one work order costing 100 or more,” you do not need columns from the matching work orders and you do not care how many matches exist. EXISTS expresses that directly. It is a logical semijoin: an outer technician qualifies once if at least one inner row satisfies the correlation.

SELECT t.technician_id, t.display_nameFROM technicians AS tWHERE EXISTS (  SELECT 1  FROM work_orders AS w  WHERE w.technician_id = t.technician_id    AND w.labor_cost >= 100)ORDER BY t.technician_id;

The subquery is correlated because it refers to t.technician_id from the outer query. SQL describes the logical relationship; MariaDB is free to transform the physical execution. Do not assume the server literally reruns a naïve nested query once for each technician.

5. IN, NOT IN, EXISTS and NULL semantics

IN (subquery) is also commonly eligible for semijoin optimization when it represents an existence question. The key semantic trap is anti-membership: NOT IN can become UNKNOWN if the inner set contains NULL. NOT EXISTS often states “no matching row exists” more safely because the correlation condition itself defines the match.

SELECT t.technician_id, t.display_nameFROM technicians AS tWHERE t.technician_id IN (  SELECT w.technician_id  FROM work_orders AS w  WHERE w.status='open')ORDER BY t.technician_id;SELECT t.technician_id, t.display_nameFROM technicians AS tWHERE NOT EXISTS (  SELECT 1  FROM work_orders AS w  WHERE w.technician_id=t.technician_id)ORDER BY t.technician_id;

6. Derived tables create a named intermediate relation

A derived table is a subquery in the FROM clause. It can make a multi-step calculation easier to reason about, for example pre-aggregating event hours per work order before joining that single row per work order back to the work-order table. Depending on query shape and optimizer rules, MariaDB may merge a derived table into the outer query or materialize it into an internal temporary result.

SELECT w.work_order_id, w.status, e.total_hoursFROM work_orders AS wLEFT JOIN (  SELECT work_order_id, SUM(hours_spent) AS total_hours  FROM work_order_events  GROUP BY work_order_id) AS e  ON e.work_order_id = w.work_order_idORDER BY w.work_order_id;

The derived table intentionally has one row per work order. That keeps the outer result at one row per work order and avoids multiplying labor-cost fields by the number of events. The optimizer may change how this is executed, but the relational grain is explicit in the SQL.

7. Observe semijoin/materialization rather than guessing

MariaDB documents several semijoin execution strategies, including table pullout, FirstMatch, LooseScan, materialization and DuplicateWeedout. For suitable uncorrelated IN subqueries, materialization can build a temporary unique set and join against it. The correct strategy depends on statistics, indexes and the query; this course does not promote a universal favorite.

EXPLAIN FORMAT=JSONSELECT t.technician_id, t.display_nameFROM technicians AS tWHERE t.technician_id IN (  SELECT w.technician_id  FROM work_orders AS w  WHERE w.labor_cost >= 100);ANALYZE FORMAT=JSONSELECT t.technician_id, t.display_nameFROM technicians AS tWHERE t.technician_id IN (  SELECT w.technician_id  FROM work_orders AS w  WHERE w.labor_cost >= 100);

Look for evidence that the subquery has been transformed or materialized, but do not write tests that require one exact JSON-plan shape forever. Plan output is diagnostic evidence and can change with server version, statistics or indexes even when results remain correct.

8. Hands-on acceptance lab

1. Predict the row count of an INNER JOIN between technicians and work orders, then run it.
2. Use LEFT JOIN to keep every technician and verify the inactive technician remains visible.
3. Run the intentionally wrong WHERE-filtered LEFT JOIN and explain which rows disappear.
4. Repair the predicate placement in ON.
5. Write the “has expensive work” requirement with EXISTS and with IN; compare results.
6. Use NOT EXISTS to find technicians with no work and explain why nullable work_orders.technician_id makes careless NOT IN reasoning dangerous.
7. Pre-aggregate event hours in a derived table.
8. Run EXPLAIN/ANALYZE on one semijoin-shaped query and record what the target server actually chose.

9. Summary and bridge

Join correctness starts with result grain. INNER JOIN keeps matches; LEFT JOIN also preserves unmatched left rows; predicate placement determines whether a condition defines matching or final filtering. EXISTS and semijoin-shaped IN express existence without requiring duplicate-producing joins. Derived tables give intermediate calculations an explicit grain, while MariaDB may merge or materialize them physically.

The next lesson names intermediate results with common table expressions and then makes those expressions recursive. That lets ServiceHub traverse team hierarchies, but recursion adds new correctness hazards: termination, cycles, inferred column width and depth limits.