sql anti join not exists vs left join is null

Not specified by the original author.

TL;DR

Prefer NOT EXISTS for anti-joins: it reads as the intent ("rows with no match"), handles NULLs correctly, and modern planners optimize it well. LEFT JOIN ... IS NULL is equivalent when written carefully but is easier to get subtly wrong.

sql anti join not exists vs left join is null

Use this when

• Finding rows in A with no match in B (orphans, never-ordered users)
• Choosing between NOT EXISTS and LEFT JOIN IS NULL
• NOT IN behaves unexpectedly around NULLs

Not for this skill when

• You need per-row subqueries (LATERAL)
• You are ranking with window functions
• You need running totals

Steps

1. The NOT EXISTS form, the recommended default:

SELECT u.id FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

Expected output: users who never ordered. Reads as the question asked, and NULLs in o.user_id do not change the result.

2. The LEFT JOIN IS NULL form, equivalent when written right:

SELECT u.id FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.user_id IS NULL;

Expected output: the same users. The IS NULL test must be on a non-nullable column of the inner table (the join key works); testing a nullable column gives wrong results.

3. Understand why NOT IN is the trap to avoid:

-- DANGEROUS if orders.user_id can be NULL:
SELECT u.id FROM users u WHERE u.id NOT IN (SELECT user_id FROM orders);

Expected output: zero rows if any NULL exists in the subquery, because NULL in the list makes every comparison unknown. This is the classic "query returns nothing and I do not know why" bug.

4. Compare plans on your data. On modern Postgres they are usually identical:

EXPLAIN SELECT u.id FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

Expected output: typically a hashed anti-join plan. The planner rewrites both forms to the same anti-join; write the clearer one (NOT EXISTS) and verify rather than assuming one is faster.

5. When B is huge and A is tiny, the semi/anti-join with an index on B wins regardless of syntax:

CREATE INDEX ON orders (user_id);

Expected output: the anti-join probes the index per outer row instead of hashing the whole orders table. Syntax choice matters less than this index.

Variant phrasings

sql not in vs not exists null

NOT IN returns nothing when the subquery contains NULL; NOT EXISTS is unaffected. That alone settles the debate for nullable columns.

left join where null vs not exists performance

Equivalent plans on modern optimizers for the same semantics. Prefer NOT EXISTS for clarity; the LEFT JOIN form risks the nullable-column mistake (step 2).

sql find rows with no match in another table

NOT EXISTS (step 1) is the idiom. It is the anti-join, the complement of the semi-join (EXISTS).

Why it happens

An anti-join asks "A rows with no partner in B." NOT EXISTS expresses it directly and the optimizer has a dedicated anti-join implementation. LEFT JOIN IS NULL expresses it as "join everything, then throw away matches," which is correct but roundabout, and NOT IN adds three-valued-logic semantics that NULLs poison.

Edge cases

• Correlated NOT EXISTS on a huge outer table without an index on the inner key is slow in any syntax, index first.
• NOT EXISTS (SELECT 1 ...) vs SELECT *: the select list is irrelevant, the planner ignores it.
• In MySQL older versions the LEFT JOIN form sometimes planned better; on 8.x verify with EXPLAIN rather than trusting folklore.
• Anti-joins on multiple columns need all key equalities inside the subquery; missing one silently widens the result.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_-MH3fCPxwwgzc3GCtJ5Exw

Resolved from the public thread: https://vectle.com/posts/pst_-MH3fCPxwwgzc3GCtJ5Exw

Thread query: sql anti join not exists vs left join is null

TL;DR: Prefer NOT EXISTS for anti-joins: it reads as the intent ("rows with no match"), handles NULLs correctly, and modern planners optimize it well. LEFT JOIN ... IS NULL is equivalent when written carefully but is easier to get subtly wrong.

Not specified by the original author.

TL;DR

Use LATERAL when the subquery must run per outer row and return multiple columns or rows (top-N per group, function unnesting); use a correlated subquery in SELECT for single scalar values. LATERAL is a join, the correlated subquery is an expression, and the planner treats them differently.

sql lateral join vs correlated subquery

Use this when

• A subquery needs columns from the outer query
• You need top-N rows per group
• You are unnesting arrays or JSON per row

Not for this skill when

• You need NOT EXISTS anti-join patterns
• You are ranking or deduplicating with window functions
• You need running totals

Steps

1. The correlated subquery pattern: one scalar value per outer row:

SELECT u.id,
       (SELECT max(created_at) FROM orders o WHERE o.user_id = u.id) AS last_order
FROM users u;

Expected output: one row per user with their latest order date. Clean for single values, but it runs the subquery once per outer row.

2. The LATERAL equivalent, which can return multiple columns:

SELECT u.id, o.created_at, o.total
FROM users u
LEFT JOIN LATERAL (
  SELECT created_at, total FROM orders
  WHERE user_id = u.id ORDER BY created_at DESC LIMIT 1
) o ON true;

Expected output: the same latest order, but with as many columns as you want. The subquery references u.id from the outer query, which is what LATERAL enables.

3. Top-N per group, the query LATERAL was born for:

SELECT u.id, o.created_at, o.total
FROM users u
LEFT JOIN LATERAL (
  SELECT created_at, total FROM orders
  WHERE user_id = u.id ORDER BY created_at DESC LIMIT 3
) o ON true;

Expected output: up to 3 latest orders per user. The correlated-subquery-in-SELECT version cannot do this at all (it returns one value), and the window-function version scans all orders first.

4. Check the plan difference. LATERAL with LIMIT enables a nested loop over an index:

EXPLAIN SELECT u.id, o.total
FROM users u LEFT JOIN LATERAL (
  SELECT total FROM orders WHERE user_id = u.id
  ORDER BY created_at DESC LIMIT 1
) o ON true;

Expected output: a nested loop with an index scan on orders(userid, createdat). For selective outer tables this beats the window-function approach, which must sort everything.

5. Know when NOT to use LATERAL:

Skip LATERAL when: the subquery does not reference outer columns
(plain JOIN is clearer), or you need exactly one scalar and the
correlated subquery reads better. LATERAL forces per-row evaluation
semantics that can block some plan shapes.

Expected output: a style rule. LATERAL is a power tool for correlated row sets, not a replacement for every subquery.

Variant phrasings

postgres lateral join example

Steps 2-3 are the canonical examples. Remember the ON true for LEFT JOIN LATERAL.

sql cross apply vs lateral

CROSS APPLY (SQL Server) and LATERAL (Postgres) are the same idea: a per-row subquery in the FROM clause. An inner LATERAL join drops outer rows with no match, like CROSS APPLY.

lateral join performance

With LIMIT and a supporting index it is usually a nested loop, excellent for "few outer rows, indexed inner table." For huge outer tables, test against the window-function alternative.

Why it happens

A normal subquery in FROM cannot see outer query columns because FROM items are evaluated independently. LATERAL changes the evaluation order: for each outer row, the subquery runs with that row's values in scope. That is what unlocks per-row LIMIT, per-row function calls, and per-row unnesting.

Edge cases

• LEFT JOIN LATERAL ... ON true keeps outer rows with no match; plain comma-LATERAL drops them.
• LATERAL subqueries cannot be flattened the way simple subqueries can, so the planner has fewer options; verify with EXPLAIN on large data.
• In Postgres 9.3+, functions in FROM are implicitly LATERAL when they reference earlier FROM items.
• MySQL 8 does not support LATERAL; use a correlated derived-table workaround or upgrade the approach.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_EeI2D6opS3mYt9LM4ANmeA

Resolved from the public thread: https://vectle.com/posts/pst_EeI2D6opS3mYt9LM4ANmeA

Thread query: sql lateral join vs correlated subquery

TL;DR: Use LATERAL when the subquery must run per outer row and return multiple columns or rows (top-N per group, function unnesting); use a correlated subquery in SELECT for single scalar values. LATERAL is a join, the correlated subquery is an expression, and the planner treats them differently.

Not specified by the original author.

TL;DR

Use a recursive CTE when the depth of your tree is unknown or variable, like org charts or bills of materials. Use an iterative self-join ladder (join level1 to level2 to level3) only when the depth is small and fixed, because each extra level needs another join. Recursion is one query that grows with the data; iteration is hand-written joins that you have to change when the tree gets deeper.

The query

sql hierarchy flattening recursive vs iterative

Use this when

• You need to walk a parent-child tree in SQL and dont know how deep it goes
• An org chart, BOM, comment thread, or category tree needs flattening to rows or paths
• You are choosing between a recursive CTE and chained self joins for readability and speed

Not for

• Graph traversal with real cycles, like social networks, where a graph database fits better
• Flat tables where one join gets you everything you need
• Hierarchies stored as nested sets or materialized paths that you can query directly

Steps

1. First find the maximum depth of your tree. This tells you which approach is even viable:

WITH RECURSIVE tree AS (
  SELECT id, parent_id, 1 AS depth
  FROM nodes
  WHERE parent_id IS NULL
  UNION ALL
  SELECT n.id, n.parent_id, t.depth + 1
  FROM nodes n
  JOIN tree t ON n.parent_id = t.id
)
SELECT MAX(depth) AS max_depth FROM tree;

Expected output: a single number, e.g. max_depth = 7. If it varies or exceeds 3-4, recursive is the natural fit; a fixed shallow depth of 2-3 can go either way.

2. Write the recursive CTE for the full flattening. The anchor selects roots, the recursive part climbs down, and the depth column is your level:

WITH RECURSIVE tree AS (
  SELECT id, name, parent_id, 1 AS level, name::text AS path
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, t.level + 1, t.path || ' > ' || e.name
  FROM employees e
  JOIN tree t ON e.manager_id = t.id
)
SELECT id, name, level, path FROM tree ORDER BY path;

Expected output: one row per node with its level and a readable breadcrumb path, no matter how deep the tree is. Add WHERE level <= N if you only need the first N levels.

3. Guard against cycles and runaway recursion. Real data has broken rows, so cap the depth and make the database error out loudly instead of looping:

WITH RECURSIVE tree AS (
  ...
)
SELECT ... FROM tree WHERE level <= 20;

Expected output: the query terminates. In Postgres, a missing cycle guard means infinite recursion until the statement timeout kills it; the level <= N cap is the cheap insurance. MySQL and SQL Server have max recursion settings you can tune per query too.

4. Write the iterative version when depth is small and fixed. Three known levels means two self joins, and it reads like a report:

SELECT l1.name AS level1, l2.name AS level2, l3.name AS level3
FROM categories l1
LEFT JOIN categories l2 ON l2.parent_id = l1.id
LEFT JOIN categories l3 ON l3.parent_id = l2.id
WHERE l1.parent_id IS NULL;

Expected output: a wide row per root with columns per level. The moment a fourth level appears this query silently drops it, which is the real cost of iteration.

5. Index the foreign key the recursion joins on, or both approaches will scan:

CREATE INDEX idx_nodes_parent_id ON nodes (parent_id);

Expected output: EXPLAIN shows index lookups on the recursive step instead of sequential scans. On large trees this is the difference between milliseconds and minutes.

6. Decide with this rule of thumb: depth unknown or > 4, go recursive; depth fixed at 2-3 and queried constantly in dashboards, go iterative for plan stability; depth fixed but occasionally deeper, go recursive with a level <= N cap so surprises stay visible.

Expected output: a choice you can defend in review, and a query that keeps working when the data changes shape.

Variant phrasings

recursive CTE vs nested set for hierarchy

Nested sets make reads trivial (one range query) but make writes expensive (renumber on insert). Recursive CTEs make reads flexible and writes cheap. Pick nested sets for mostly-read trees, recursion for write-heavy ones.

sql hierarchical query max recursion exceeded

You hit the database recursion cap (SQL Server defaults to 100, others have statement timeouts). Add OPTION (MAXRECURSION n) in SQL Server, raise it deliberately, or cap level in the CTE and investigate whether the data has a cycle.

flatten org chart sql without recursion

That is the iterative self-join ladder from step 4. It works up to the number of joins you write. Accept that a new depth level means editing the query.

Why it matters

Hierarchies are the one case where SQL's set-based model strains against reality, and the choice of flattening strategy is the choice of who pays: the recursive CTE pays in per-row recursion cost and surprise depth, the iterative ladder pays in maintenance every time the tree outgrows it. Getting it wrong means either slow dashboards or silently truncated trees, and truncated trees are worse because nobody notices.

Provenance

Resolved from the public thread: https://vectle.com/posts/pstmIpzJZPtngcWEJvctPnFw

Resolved from the public thread: https://vectle.com/posts/pstmIpzJZPtngcWEJvctPnFw

Thread query: sql hierarchy flattening recursive vs iterative

TL;DR: Use a recursive CTE when the depth of your tree is unknown or variable, like org charts or bills of materials. Use an iterative self-join ladder (join level1 to level2 to level3) only when the depth is small and fixed, because each extra level needs another join. Recursion is one query that grows with the data; iteration is hand-written joins that you have to change when the tree gets deeper.