easy Sales Person

You build the set of sales_ids that did trade with RED, then keep every salesperson whose id is provably absent from that set — an anti-join (set difference) over the join Orders ⋈ Company.

Problem

Three tables. Find the names of salespersons who had no orders with the company named "RED".

SalesPerson(sales_id PK, name, salary, commission_rate, hire_date)
Company(com_id PK, name, city)
Orders(order_id PK, order_date, com_id FK→Company, sales_id FK→SalesPerson, amount)

The task is a set difference: all salespersons minus salespersons linked to RED. The only subtlety is how SQL evaluates "not in this set" when the set can contain unknowns.

The data we will trace

Concrete rows (the canonical LeetCode 607 fixture):

Only orders o3 and o4 point at company 1 (RED). Their salespersons are 1 (John) and 4 (Pam). So the answer is everyone else: Amy, Mark, Alex.

Step 1 — who sold to RED

Join Orders to Company on com_id, keep only the RED company, project the salesperson id.

SELECT o.sales_id
FROM   Orders o
       JOIN Company c ON c.com_id = o.com_id
WHERE  c.name = 'RED';

Trace: o1→com3 (YELLOW, drop), o2→com4 (GREEN, drop), o3→com1 (RED, keep → 1), o4→com1 (RED, keep → 4). Result set: {1, 4}.

Step 2 — keep everyone not in that set

SELECT s.name
FROM   SalesPerson s
WHERE  s.sales_id NOT IN (
           SELECT o.sales_id
           FROM   Orders o
                  JOIN Company c ON c.com_id = o.com_id
           WHERE  c.name = 'RED');

Row by row against {1, 4}: John(1)→in set, drop. Amy(2)→keep. Mark(3)→keep. Pam(4)→in set, drop. Alex(5)→keep. Output: Amy, Mark, Alex. Correct for this data.

Why the naive version is fragile

The query above is correct only because Orders.sales_id happens to hold no NULLs here. The pattern itself hides SQL's single most-missed gotcha.

x NOT IN (a, b, c) desugars to x ≠ a AND x ≠ b AND x ≠ c. If any element is NULL, that comparison is not FALSE — it is UNKNOWN (three-valued logic). And TRUE AND UNKNOWN = UNKNOWN, which is not TRUE, so the row is excluded. The result: a single NULL anywhere in the subquery makes NOT IN return zero rows, regardless of the real data. No error, no warning — just an empty result that looks plausible until production traffic produces a NULL sales_id.

The robust formulations sidestep three-valued logic entirely. NOT EXISTS is the canonical fix:

-- NOT EXISTS: NULL-safe, and usually the planner's friend
SELECT s.name
FROM   SalesPerson s
WHERE  NOT EXISTS (
           SELECT 1
           FROM   Orders o
                  JOIN Company c ON c.com_id = o.com_id
           WHERE  c.name = 'RED'
             AND  o.sales_id = s.sales_id);

NOT EXISTS asks "is there a matching RED order for this salesperson?" A NULL o.sales_id never equals s.sales_id, so it simply fails to match — it cannot poison other rows. The anti-join via LEFT JOIN ... IS NULL is equivalent:

-- Anti-join: keep salespersons with NO surviving RED match
SELECT s.name
FROM   SalesPerson s
       LEFT JOIN (Orders o JOIN Company c
                    ON c.com_id = o.com_id AND c.name = 'RED')
              ON o.sales_id = s.sales_id
WHERE  o.order_id IS NULL;

The RED filter must constrain which orders count before the anti-join, so collapse Orders ⋈ Company(RED) into one joined set and LEFT JOIN the salesperson to that, keeping those with no surviving match. A naive version — two separate LEFT JOINs with c.name='RED' in the second ON — is wrong: a salesperson who has both a RED and a non-RED order (here Pam: RED order o4 and YELLOW order o1) keeps her non-RED row with a NULL company, which slips through ... IS NULL and re-admits her. Restrict the probe side first, then test for the absence of a match.

Pitfalls

• NULL in NOT IN → zero rows. The headline bug above. It is silent. If the subquery column is nullable, never use NOT IN — use NOT EXISTS. (Add WHERE col IS NOT NULL to the subquery only as a last resort; NOT EXISTS is cleaner and faster.)
• IN with NULL behaves differently. A NULL in an IN list can only ever turn a non-match into UNKNOWN; it cannot manufacture a match. So IN looks "safe" and lulls you — then you flip to NOT IN and the asymmetry bites.
• Filtering the outer table on the joined company in a LEFT JOIN. Writing WHERE c.name = 'RED' instead of putting it in the ON clause silently converts the outer join back into an inner join and drops exactly the rows you wanted to keep.
• Forgetting salespersons with zero orders. Alex has no orders at all. NOT IN / NOT EXISTS include him correctly; a naive inner-join approach ("join Orders, exclude RED") would wrongly omit anyone who never placed an order.
• Performance: on large tables, planners often optimize NOT EXISTS into an efficient anti-join (hash/merge), while a correlated NOT IN over a nullable column can degrade to a row-by-row scan. Correctness and speed point the same way here.

Takeaways

• Anti-joins are set difference. "Find rows with no match" = NOT EXISTS or LEFT JOIN ... IS NULL. Reach for these by default.
• NOT IN is the trap. One NULL in the subquery collapses the whole result to empty, because x ≠ NULL is UNKNOWN, never TRUE. Treat any nullable subquery column as a landmine.
• Three-valued logic is the real lesson. SQL predicates are TRUE / FALSE / UNKNOWN, and WHERE keeps only TRUE — internalize this and a whole class of "query returns nothing" bugs disappears.
• Put join filters in ON for outer joins. Conditions on the optional side belong in ON, not WHERE, or the join silently turns inner.

Problem from LeetCode 607 "Sales Person." NULL semantics of NOT IN per the SQL standard's three-valued logic (ISO/IEC 9075); equivalence of NOT EXISTS and anti-join, and planner behavior, follow the PostgreSQL and SQL Server documentation on subqueries and join elimination. Three-valued-logic framing draws on Joe Celko, "SQL for Smarties." Re-authored and deepened for this guide: added the NULL-propagation failure mode the original omitted, the NULL-safe NOT EXISTS and LEFT JOIN ... IS NULL rewrites, a traced example, and the three-valued-logic diagram.