INNER JOIN and EXISTS behave differently in several non-obvious or advanced scenarios. These edge cases matter when dealing with NULLs, correlated subqueries, multi-column joins, performance quirks, and MySQL optimizer rewrites. INNER JOIN cannot match NULL values, because NULL = NULL is never true. EXISTS ignores the comparison result if the subquery returns *any* row. **Result:** EXISTS can succeed even when the join condition involves NULL comparisons, while INNER JOIN returns no match. SELECT * FROM a WHERE EXISTS (SELECT 1 FROM b WHERE b.value IS NULL); -- Works if b has a NULL SELECT * FROM a JOIN b ON a.value = b.value; -- NULL never joins INNER JOIN repeats the left-side row for every match. EXISTS returns the row once, regardless of the number of matches. This significantly impacts aggregates, pagination, and DISTINCT usage. EXISTS can reference outer query columns and short-circuit on first match. INNER JOIN cannot use short-circuit logic; it must process all matches unless optimized away. Correlated EXISTS often outperforms JOINs on large selective datasets. SELECT u.user_id FROM users u WHERE EXISTS ( SELECT 1 FROM logins l WHERE l.user_id = u.user_id AND l.login_time >= NOW() - INTERVAL 30 DAY ); **LEFT JOIN + IS NULL fails when the joined column contains NULLs**. **NOT EXISTS works correctly even with NULL values.** Thus, NOT EXISTS is logically safer. LEFT JOIN payments p ON o.id = p.order_id WHERE p.order_id IS NULL; -- Fails if p.order_id is NULL but row exists WHERE NOT EXISTS (SELECT 1 FROM payments p WHERE p.order_id = o.id); -- Always correct MySQL often rewrites EXISTS into a SEMI-JOIN internally. MySQL sometimes rewrites certain JOINs into EXISTS-style lookups. Because of optimizer rewrites, performance differences are smaller for tiny tables. Since EXISTS only checks for existence, ORDER BY inside the subquery is ignored. INNER JOIN can use ORDER BY at the main query level to order joined rows. INNER JOIN can distort aggregates (SUM, COUNT) due to row multiplication. EXISTS avoids distortion since it doesn't duplicate rows. This makes EXISTS better for queries like “count customers who placed orders”. SELECT COUNT(*) FROM customers c JOIN orders o ON c.id = o.customer_id; -- Overcounts if multiple orders SELECT COUNT(*) FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id); EXISTS wins when: the joined table is large, indexed, and selective. INNER JOIN wins when: you need data from both sides and indexes support merging. NOT EXISTS is vastly faster than NOT IN or LEFT JOIN in many cases. For huge tables, EXISTS reduces memory usage because it avoids materializing join buffers. INNER JOIN is best when retrieving data from both tables and relationships are simple. EXISTS is best for filtering, avoiding duplicates, handling NULLs safely, and improving performance on large datasets. In edge cases involving NULLs, correlated logic, and aggregation, EXISTS is almost always the safer and more predictable choice.

Complex & Edge Cases

Difficulty: 7/10

transaction isolation, schema migrations, query performance