EXISTS and NOT EXISTS

EXISTS evaluates a correlated subquery and returns TRUE if the subquery produces at least one row, FALSE if it produces zero rows. It does not examine the values returned by the subquery — only whether any rows exist. The database stops evaluating the subquery as soon as the first matching row is found (short-circuit behaviour), making it efficient for existence checks.

SELECT 1 inside EXISTS is a universal convention that signals intent: "I only care whether rows exist, not what values they contain." The 1 is a constant — it carries no information. Writing SELECT 1 rather than SELECT * or SELECT column_name communicates to any reader that the subquery's output is irrelevant and the purpose is purely an existence check. This convention is widely recognised and expected in professional SQL code.

Technically, SELECT *, SELECT column, SELECT NULL, and SELECT 1 all produce equivalent results inside EXISTS. The database optimiser ignores the SELECT list entirely when evaluating EXISTS — it only cares whether the WHERE conditions inside the subquery are satisfied for at least one row. SELECT 1 is the standard precisely because it makes this intent explicit. Using SELECT * inside EXISTS can mislead readers into thinking the column values matter, and in older database versions, SELECT * could cause unnecessary column retrieval overhead (though modern optimisers eliminate this).

NOT IN fails silently when the subquery contains NULL values, returning zero rows for every comparison — a critical correctness bug with no error message. The mechanism: NOT IN (1, 2, NULL) is evaluated as NOT (col = 1 OR col = 2 OR col = NULL). Since col = NULL evaluates to NULL in SQL's three-valued logic (not FALSE), the entire OR expression can evaluate to NULL for any col value, and NOT NULL = NULL (still not TRUE). WHERE discards all NULL-result rows, returning zero results for the entire query — even when non-NULL unmatched rows clearly exist.

NOT EXISTS is immune to this problem. It evaluates row-by-row whether a matching row exists. A NULL value in the inner table's column means "this particular row has no value for this column" — it does not affect whether other rows match. The existence check for the outer row is independent of NULLs in non-matching rows.

The silent failure mode of NOT IN makes it particularly dangerous in production. A schema change that adds a nullable foreign key column, a bulk import that inserts a row with NULL in the join column, or a data quality issue that introduces unexpected NULLs — any of these can cause a previously working NOT IN query to silently start returning zero rows with no error, warning, or any visible indication that the query's behaviour changed. NOT EXISTS never exhibits this behaviour. Professional SQL standards at most companies mandate NOT EXISTS (or LEFT JOIN IS NULL) for all anti-join queries, with NOT IN only permitted when a NOT NULL constraint on the join column is verified and documented.

Relational division finds entities that satisfy all members of a set — "which customers have ordered every product in the Dairy category?", "which employees have completed all required trainings?", "which stores have sold every product?" It is the relational equivalent of universal quantification: for all Y in the required set, X has done Y.

The double NOT EXISTS pattern implements this by expressing the condition logically: "find X for which there does NOT EXIST a required Y that X has NOT completed." This translates to two nested NOT EXISTS: the outer NOT EXISTS checks "is there a required item (Y)?", the inner NOT EXISTS checks "has X done Y?" If the outer NOT EXISTS is satisfied — no required item exists that X has not done — then X has done all required items.

SQL implementation: SELECT * FROM customers AS c WHERE NOT EXISTS (SELECT 1 FROM products AS p WHERE p.category = 'Dairy' AND NOT EXISTS (SELECT 1 FROM order_items AS oi JOIN orders AS o ON oi.order_id = o.order_id WHERE oi.product_id = p.product_id AND o.customer_id = c.customer_id AND o.order_status = 'Delivered')). Read inside-out: the innermost subquery asks "has this customer ordered this Dairy product?" The middle NOT EXISTS makes it "is there a Dairy product this customer has NOT ordered?" The outer NOT EXISTS makes it "is there NO Dairy product this customer hasn't ordered?" — which is TRUE only when the customer has ordered all Dairy products. This is the only clean SQL implementation of relational division; JOIN-based alternatives are more complex and fragile.

Combining EXISTS and NOT EXISTS in WHERE expresses compound existence conditions — "has done X but never done Y", "is active but has not completed Z", "was created but has not been processed." These are very common in analytics and CRM segmentation.

The pattern: WHERE EXISTS (...has the required condition...) AND NOT EXISTS (...lacks the disqualifying condition...). Examples: customers who have placed at least one order (EXISTS) but have never cancelled one (NOT EXISTS) — loyal customers. Employees who are assigned to a store (EXISTS) but have never managed a project (NOT EXISTS) — candidates for project lead rotation. Products that have been sold (EXISTS) but never returned (NOT EXISTS) — reliable products for promotion.

The compound pattern is more readable than the JOIN alternative. WHERE EXISTS (...) AND NOT EXISTS (...) reads almost like English — "customers who have ordered AND have not cancelled." The equivalent with JOINs requires careful outer join construction and NULL checking, which is less immediately clear. Use EXISTS + NOT EXISTS whenever a business question involves multiple independent existence conditions that combine with AND — each condition naturally maps to one EXISTS or NOT EXISTS clause.

For most queries on modern databases, EXISTS and the equivalent JOIN produce the same query plan — the optimiser recognises the semi-join pattern and converts both to the same execution strategy. The logical difference (EXISTS short-circuits, JOIN processes all rows) is typically erased by the optimiser's plan selection.

Where EXISTS has a genuine performance advantage over JOIN: when the subquery's early exit (short-circuit) is preserved by the optimiser and matches are very common. If 90% of customers have orders, EXISTS finds one order per customer and stops — processing on average ~1.1 inner rows per outer row. A JOIN processes all matching orders per customer, which could be 5, 10, or 100 rows per customer. For large tables with many matches per outer row, this can be significant.

Where JOIN can be faster: when the JOIN uses a hash join with a small build side — the optimiser builds a hash table from the inner table and probes it for each outer row in O(1) per probe. For EXISTS on large tables with complex subqueries that the optimiser cannot convert to a hash join, the correlated execution (one subquery per outer row) can be slower. The practical guidance: both approaches are equivalent for most production queries on indexed tables. Use EXISTS when existence semantics are the intent (clearest code), use JOIN when you need columns from the right table (EXISTS cannot project them). If performance matters, measure both with EXPLAIN ANALYZE on realistic data volumes.

Cause: The correlated condition inside NOT EXISTS is missing. NOT EXISTS (SELECT 1 FROM orders) without a WHERE clause linking to the outer query checks whether the orders table has any rows at all — which is always TRUE when the table is non-empty. NOT TRUE = FALSE for every outer row, so no customers are excluded. The subquery needs to be correlated to the current outer row.

Fix: Add the correlated WHERE condition: WHERE NOT EXISTS (SELECT 1 FROM orders AS o WHERE o.customer_id = c.customer_id). The inner WHERE must reference the outer alias (c.customer_id) to make the check specific to the current outer row. Verify by testing the inner query manually: SELECT 1 FROM orders WHERE customer_id = 1 — this should return rows for customers who have ordered and no rows for those who have not.

Cause: The NOT EXISTS subquery has a condition that is always satisfied or references the wrong column. A common mistake: NOT EXISTS (SELECT 1 FROM orders WHERE customer_id IS NOT NULL) — this checks whether any order exists with a non-NULL customer_id, which is almost always TRUE, making NOT EXISTS always FALSE and returning zero rows. Another cause: missing the correlated link so it tests a global condition.

Fix: Verify the correlated condition specifically links to the outer row: WHERE NOT EXISTS (SELECT 1 FROM orders AS o WHERE o.customer_id = c.customer_id). Test by temporarily changing NOT EXISTS to EXISTS — it should return the rows you want to exclude. If EXISTS returns the expected rows, NOT EXISTS should return the complement. Also check whether the outer table has any rows that genuinely have no matches in the inner table — NOT EXISTS correctly returns zero rows if all outer rows do have matches.

Cause: The correlated column in the EXISTS subquery's WHERE is not indexed in the inner table. Without an index, each outer row evaluation requires a full scan of the inner table. For N outer rows, total cost is N × full_table_scan — O(n²). With 100,000 customers and 1,000,000 orders, this means 100,000 full scans of 1,000,000 rows = 100 billion row comparisons.

Fix: Create an index on the correlated column in the inner table: CREATE INDEX idx_orders_customer_id ON orders(customer_id). With this index, each EXISTS check is one index lookup — O(log n) instead of O(n). Also add indexes on any additional filter columns used inside the subquery: CREATE INDEX idx_orders_status_customer ON orders(order_status, customer_id) for EXISTS queries that filter on both status and customer_id. Use EXPLAIN ANALYZE to verify the index is being used after creation.

Cause: The double NOT EXISTS logic is inverted or the correlated conditions reference the wrong tables. The outer NOT EXISTS should reference the required set (e.g., Dairy products), and the inner NOT EXISTS should reference the action table (e.g., order_items). Common mistake: both NOT EXISTS reference the same table, making the logic wrong. Another cause: the required set is empty — if no Dairy products exist, NOT EXISTS (SELECT 1 FROM products WHERE category = 'Dairy' AND NOT EXISTS (...)) is vacuously TRUE for all customers.

Fix: Read the double NOT EXISTS inside-out to verify logic: inner NOT EXISTS should ask 'has this customer ordered this product?', outer NOT EXISTS should ask 'does a product exist that the customer has NOT ordered?'. Trace through one specific customer manually: pick a customer_id, run the inner NOT EXISTS subquery for each Dairy product and verify it correctly returns rows when the customer hasn't ordered that product. Also verify the required set is non-empty: SELECT COUNT(*) FROM products WHERE category = 'Dairy'.

Cause: The EXISTS subquery is not specific enough — it matches rows that should not be included in the update. A missing filter condition in the subquery, or a correlated condition that is too broad, causes EXISTS to return TRUE for outer rows that should not be updated.

Fix: Always preview an EXISTS UPDATE as a SELECT first: convert UPDATE table SET col = val WHERE EXISTS (...) to SELECT * FROM table WHERE EXISTS (...) and verify exactly which rows would be affected. The SELECT shows you the scope before any modification. If the SELECT returns unexpected rows, tighten the EXISTS subquery's WHERE conditions. Add AND o.order_status = 'Delivered' or other filters to make the existence check more specific. Only run the UPDATE after the SELECT confirms the exact expected rows.

Write three queries using EXISTS and NOT EXISTS: (1) Find all stores that have at least one employee AND at least one delivered order worth more than ₹800. Show store_id, city, and store_name. (2) Find all customers who have placed at least one delivered order but have NEVER used COD (Cash on Delivery) as a payment method. Show customer_id, full name, city, and loyalty_tier. (3) Find all products that exist in the products table but have never appeared in any delivered order. Then also find products that appear in delivered orders but have a unit_price below ₹30 AND have been ordered more than once. Combine both using UNION ALL with a 'reason' column labelling each set.