Subqueries, EXISTS, IN, Correlation, Semi/Anti Join Patterns, and Scalar Subquery Risks

Learning outcomes

The ServiceHub compliance team needs “customers with no blocked status.” One implementation uses NOT IN against an imported list and suddenly returns zero rows after the import contains a single NULL. Another uses a scalar subquery that works in test until a work order receives a second visit and production throws “Subquery returned more than 1 value.” This lesson treats subqueries as result-shape contracts, not shortcuts.

Lab bootstrap: make the lesson independently runnable

If you completed Lesson 2, this table already exists. The idempotent bootstrap below creates the minimum visit data only when it is absent, so this lesson can also be opened directly after the core ServiceHubLab setup.

USE ServiceHubLab;GOIF SCHEMA_ID(N'lab05') IS NULL EXEC(N'CREATE SCHEMA lab05 AUTHORIZATION dbo;');GOIF OBJECT_ID(N'lab05.WorkOrderVisit',N'U') IS NULLBEGIN    CREATE 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    );    INSERT 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');END;GO

1. EXISTS asks whether at least one qualifying row exists

EXISTS(subquery) is TRUE when the subquery returns at least one row; the projected expression inside it is irrelevant to the truth test. A correlated subquery can reference columns from the outer query. Conceptually, this is a per-outer-row existence question, but the optimizer can transform it into a semi join or another equivalent physical form. Do not assume the subquery literally executes once per outer row.

USE ServiceHubLab;GOSELECT w.work_order_id, w.customer_code, w.statusFROM ops.WorkOrder AS wWHERE EXISTS(    SELECT 1    FROM lab05.WorkOrderVisit AS v    WHERE v.work_order_id = w.work_order_id)ORDER BY w.work_order_id;GOSELECT w.work_order_id, w.customer_codeFROM ops.WorkOrder AS wWHERE NOT EXISTS(    SELECT 1    FROM lab05.WorkOrderVisit AS v    WHERE v.work_order_id = w.work_order_id)ORDER BY w.work_order_id;GO

The first query returns each qualifying work order once even if it has multiple visits because existence is the requested boolean fact; it does not project the visit rows. The second is an anti-semi intent: keep work orders for which no matching visit exists.

2. IN is membership; NOT IN has a NULL trap

x IN (subquery) asks whether x matches any returned value. When the types and NULL conditions make the logic equivalent, the optimizer can often produce a strategy comparable to EXISTS. However, NOT IN combined with a NULL in the candidate set can become UNKNOWN for nonmatching values. WHERE keeps only TRUE, so an apparently reasonable anti-filter can return no rows.

USE ServiceHubLab;GODROP TABLE IF EXISTS lab05.BlockedCustomer;CREATE TABLE lab05.BlockedCustomer(    customer_code varchar(16) NULL);INSERT lab05.BlockedCustomer(customer_code)VALUES ('CUST-002'), (NULL);GO-- Deliberately dangerous when the subquery can return NULL:SELECT w.work_order_id, w.customer_codeFROM ops.WorkOrder AS wWHERE w.customer_code NOT IN      (SELECT b.customer_code FROM lab05.BlockedCustomer AS b)ORDER BY w.work_order_id;GO-- NULL-safe anti-existence formulation:SELECT w.work_order_id, w.customer_codeFROM ops.WorkOrder AS wWHERE NOT EXISTS(    SELECT 1    FROM lab05.BlockedCustomer AS b    WHERE b.customer_code = w.customer_code)ORDER BY w.work_order_id;GO

With the NULL present, the first query can return zero rows because “not equal to every value including unknown” is not TRUE. The second expresses the actual business question: there must not exist a blocklist row equal to this customer code. Another valid repair is to enforce NOT NULL in the blocklist when NULL has no business meaning, but that is a schema decision, not a query hack.

3. Scalar subqueries promise at most one value

A scalar subquery appears where one scalar expression is expected. If it returns zero rows, SQL Server supplies NULL; if it returns more than one row, execution fails. Using TOP (1) to suppress the error is safe only when you deliberately define which row wins with ORDER BY and the business rule really allows one selected row.

USE ServiceHubLab;GO-- Work order 1001 has two visits: this scalar subquery should fail.SELECT w.work_order_id,       (SELECT v.outcome        FROM lab05.WorkOrderVisit AS v        WHERE v.work_order_id = w.work_order_id) AS one_outcomeFROM ops.WorkOrder AS wWHERE w.work_order_id = 1001;GO-- Define the requested value: latest visit outcome.SELECT w.work_order_id,       (SELECT TOP (1) v.outcome        FROM lab05.WorkOrderVisit AS v        WHERE v.work_order_id = w.work_order_id        ORDER BY v.visit_started_at DESC, v.visit_id DESC) AS latest_outcomeFROM ops.WorkOrder AS wWHERE w.work_order_id = 1001;GO

The repair is not “TOP makes subqueries safe.” The repair is that the requirement has been refined from “one arbitrary outcome” to “the outcome of the latest visit,” with a deterministic tie-breaker. Lesson 4 will show APPLY as a composable way to return multiple columns from that chosen row.

4. Intent first; syntax micro-benchmarks second

Rules such as “EXISTS is always faster than IN” are not reliable. SQL Server can transform semantically equivalent formulations into similar plans, while data distribution, indexes, NULLability, correlation, and statistics determine actual cost. Choose a formulation whose truth conditions are obvious, then measure the plan and runtime evidence for the real workload.

USE ServiceHubLab;GOSET STATISTICS IO ON;SELECT w.work_order_idFROM ops.WorkOrder AS wWHERE EXISTS(    SELECT 1 FROM lab05.WorkOrderTag AS t    WHERE t.work_order_id = w.work_order_id);SELECT w.work_order_idFROM ops.WorkOrder AS wWHERE w.work_order_id IN(    SELECT t.work_order_id FROM lab05.WorkOrderTag AS t);SET STATISTICS IO OFF;GO

On this tiny dataset both may be trivial. Inspect the actual plans if available; if they are identical, that is evidence for this compilation, not a universal language law. If they differ, diagnose the exact statistics/predicate/access-path reason rather than inferring that one keyword is inherently faster.

5. Correlation does not mandate nested-loops execution

A correlated subquery references the outer row, but correlation is a logical dependency, not a required physical algorithm. The optimizer can decorrelate or transform many EXISTS/IN patterns into joins. Conversely, complex correlation can remain expensive. This distinction matters because developers sometimes rewrite clear EXISTS logic into a manual JOIN + DISTINCT based on fear of “running the subquery N times,” creating fan-out bugs from Lesson 2.

6. Hands-on verification and production judgment

USE ServiceHubLab;GOSELECT SERVERPROPERTY('ProductVersion') AS product_version,       compatibility_levelFROM sys.databasesWHERE name = DB_NAME();SELECT customer_code, COUNT(*) AS rows_in_blocklistFROM lab05.BlockedCustomerGROUP BY customer_codeORDER BY customer_code;SELECT work_order_id, COUNT(*) AS visit_countFROM lab05.WorkOrderVisitGROUP BY work_order_idORDER BY work_order_id;GO

For production, enforce NULLability and uniqueness where the business model permits it, because stronger metadata makes correctness easier and can help optimization. Do not rely on application assumptions such as “the subquery is unique” when no constraint proves it. The required lab works on free Developer/Express and needs only normal DDL/DML permissions in ServiceHubLab. No restart, trace flag, paid edition, or cloud service is required.

Authoritative references

• EXISTS — existence predicate semantics
• IN — membership and NULL caution
• NULL and UNKNOWN — three-valued logic
• Subqueries — subquery forms and restrictions
• Execution plans — optimizer implementation evidence
• SQL Server 2025 build versions — servicing baseline