Isolate non-events, dead catalog inventory, and dormant customer cohorts using the bulletproof Anti-Join formula while avoiding catastrophic three-valued logic traps.
Open in Present mode · 400 practice questions · Live class schedule · Try the playground
Jo class mein bola, yahan nukte mein likha hai.
What I said in class, written here as key points.
A four-phase operational blueprint for isolating non-events, gap records, and missing relational children.
LEFT JOIN ... WHERE right.pk IS NULL) provides guaranteed, deterministic isolation of relational absence without falling into three-valued logic traps.Transitioning from matching entity intersection to isolating what never occurred
LEFT JOIN and filter WHERE right_table.primary_key IS NULL. Because primary keys can never naturally be NULL, any NULL proves the row had zero matches.SELECT p.product_id, p.product_name, p.price, p.cost_price, ROUND( p.price - p.cost_price, 2 ) AS unit_margin FROM products.products p LEFT JOIN sales.order_items oi ON p.product_id = oi.prod_id WHERE oi.order_item_id IS NULL ORDER BY p.price DESC LIMIT 10; -- Isolate 604 dead inventory SKUs
NOT IN against subqueries that might contain NULLs. Always use the bulletproof Anti-Join formula: LEFT JOIN on child foreign key and filter WHERE child.primary_key IS NULL!Translating the real-world business need of finding missing records into formal relational algebra.
A − (A ∩ B). It isolates records in Table A that possess zero corresponding children in Table B.WHERE right_table.pk IS NULL guarantees that only genuine relational orphans survive the filter.Click join modes below to visualize the mathematical set differences between matching and absence.
LEFT JOIN preserves 100% of the parent entities, padding non-existent child foreign keys with relational NULLs.WHERE child.pk IS NULL surgically excises every matched record, leaving only the pure absence subset.How SQL engines detect parent records that possess zero corresponding relational children.
FROM parent LEFT JOIN child ON parent.id = child.parent_id. This guarantees every parent row is represented.WHERE child.id IS NULL. Primary keys are NOT NULL by definition, guaranteeing 100% accuracy.c.customer_id ASC in ORDER BY.SELECT c.customer_id, c.first_name || ' ' || c.last_name AS customer_name, c.email, c.registration_date, (DATE '2026-02-26' - c.registration_date) AS days_dormant FROM customers.customers c LEFT JOIN sales.orders o ON c.customer_id = o.cust_id WHERE o.order_id IS NULL ORDER BY c.registration_date ASC, c.customer_id ASC LIMIT 15;
Click below to see why a single NULL row collapses NOT IN queries to zero rows in three-valued logic.
val != NULL yields UNKNOWN in SQL. It is never TRUE and never FALSE.TRUE AND TRUE AND UNKNOWN evaluates to UNKNOWN. One NULL ruins the whole set.Why senior engineers ban NOT IN on subqueries and mandate the Anti-Join pattern.
id NOT IN (1, 2, NULL) expands to (id != 1) AND (id != 2) AND (id != NULL). Because id != NULL is UNKNOWN, the expression evaluates to UNKNOWN!IS NULL tests with 100% certainty.SELECT c.customer_id, c.first_name, c.email FROM customers.customers c LEFT JOIN sales.orders o ON c.customer_id = o.cust_id WHERE o.order_id IS NULL ORDER BY c.customer_id ASC LIMIT 15;
Click below to explore three major enterprise workflows powered exclusively by anti-joins.
Recovering warehouse capital by identifying SKUs with zero lifetime sales in RetailMart.
products.products to sales.order_items, not orders. An order contains many items; order_items confirms product sales directly.(price - cost_price) to evaluate locked margins and prioritize high-ticket inventory liquidation.SELECT p.product_id, p.product_name, p.price, p.cost_price, ROUND(p.price - p.cost_price, 2) AS unit_margin FROM products.products p LEFT JOIN sales.order_items oi ON p.product_id = oi.prod_id WHERE oi.order_item_id IS NULL ORDER BY p.price DESC, p.product_id ASC LIMIT 15;
Click below to analyze the conversion breakdown between acquired users and non-transacting ghost accounts.
customers.customers. Every user is tracked with a registration date.sales.orders.The critical rule governing where temporal and status predicates must reside in outer joins.
WHERE demotes the query into an INNER JOIN because NULLs fail the filter!AND o.order_date >= '2026-01-01' in the ON clause evaluates matches during the join, preserving customers who ordered in 2025.WHERE o.order_id IS NULL, cleanly capturing all users who have not transacted in 2026.SELECT c.customer_id, c.first_name || ' ' || c.last_name AS customer_name, c.tier, c.registration_date FROM customers.customers c LEFT JOIN sales.orders o ON c.customer_id = o.cust_id AND o.order_date >= DATE '2026-01-01' WHERE o.order_id IS NULL ORDER BY c.customer_id ASC LIMIT 15;
Click below to trace how the PostgreSQL 18 query planner optimizes anti-joins with early exit logic.
sales.orders) and hashes the join key (cust_id).Inspecting PostgreSQL 18 execution plans to verify cost-efficient anti-join nodes.
Hash Right Join or Hash Anti Join node, recognizing that child rows are only probed for presence.Filter: (o.order_id IS NULL) condition is executed directly as part of the join node, eliminating discarded rows before output.EXPLAIN (COSTS OFF) SELECT c.customer_id, c.first_name || ' ' || c.last_name AS customer_name FROM customers.customers c LEFT JOIN sales.orders o ON c.customer_id = o.cust_id WHERE o.order_id IS NULL LIMIT 15;
Avoid these four classic errors when constructing relational absence queries in production.
WHERE o.discount_amount IS NULL creates false positives! If an order had zero discount, it looks like an absent order. Always filter on the child primary key!WHERE o.order_date >= '2026-01-01' discards all unmatched parent rows! Child filtering conditions must stay in the ON clause to preserve parents.NOT IN to evaluate to UNKNOWN for every row, returning 0 rows silently without an error.ORDER BY registration_date leaves identical dates in arbitrary order. Always append customer_id ASC as a deterministic tie-breaker.Write and test real anti-join queries in PostgreSQL 18 across 4 difficulty tiers.
Authentic technical interview questions asked by senior engineering panels at top tech companies.
NULL, the expression x NOT IN (1, 2, NULL) evaluates to UNKNOWN for all rows, causing the query to return zero rows silently!LEFT JOIN table_b ON a.id = b.a_id WHERE b.primary_key IS NULL is 100% immune to NULL values and compiles into highly optimized anti-join hash execution plans.Click each card to test your retention of anti-join mechanics and three-valued logic rules.
The four non-negotiable architectural principles of querying relational absence.
LEFT JOIN ... ON parent.id = child.parent_id WHERE child.pk IS NULL is the universal, bulletproof standard for relational subtraction.NOT IN to evaluate to UNKNOWN, silently destroying queries without an error.ON clause. Putting them in WHERE demotes the query to an INNER JOIN.Hash Anti-Join nodes in O(N + M) time, scaling cleanly across 24 lakh rows.Production-ready SQL templates for common absence patterns across relational systems.
SELECT p.product_id, p.product_name, p.price FROM products.products p LEFT JOIN sales.order_items oi ON p.product_id = oi.prod_id WHERE oi.order_item_id IS NULL ORDER BY p.price DESC, p.product_id ASC LIMIT 10;
SELECT c.customer_id, c.first_name, c.email FROM customers.customers c LEFT JOIN sales.orders o ON c.customer_id = o.cust_id WHERE o.order_id IS NULL ORDER BY c.customer_id ASC LIMIT 10;
SELECT c.customer_id, c.first_name, c.tier FROM customers.customers c LEFT JOIN sales.orders o ON c.customer_id = o.cust_id AND o.order_date >= DATE '2026-01-01' WHERE o.order_id IS NULL ORDER BY c.customer_id ASC LIMIT 10;
EXPLAIN (COSTS OFF) SELECT c.customer_id FROM customers.customers c LEFT JOIN sales.orders o ON c.customer_id = o.cust_id WHERE o.order_id IS NULL LIMIT 10;
Three production audit challenges to test your mastery of anti-joins and relational gap detection.
customers.customers against support.tickets to identify all registered users who have NEVER submitted a customer support ticket.sales.orders to sales.returns using an anti-join to find completed orders that experienced ZERO customer returns.loyalty.members to customers and redemptions to find members with points > 2,500 who have zero lifetime redemptions.You have mastered single-sided relational absence. Next: bidirectional two-sided audits.
When either side could be missing: bidirectional relational auditing and enterprise ledger reconciliation.
a.id directly breaks on right-only rows, and how COALESCE(a.id, b.id) rescues identity.SELECT
COALESCE(o.order_id, p.order_id) AS reconciled_id,
o.net_total AS order_amount,
p.amount AS paid_amount,
CASE
WHEN o.order_id IS NOT NULL AND p.payment_id IS NOT NULL THEN 'Fully Reconciled'
WHEN o.order_id IS NOT NULL AND p.payment_id IS NULL THEN 'Pending Payment'
ELSE 'Orphan Gateway Payment'
END AS reconciliation_status
FROM sales.orders o
FULL OUTER JOIN sales.payments p
ON o.order_id = p.order_id
ORDER BY reconciled_id DESC
LIMIT 10;