sql

🔷 SQL: Anti-Join Patterns and EXISTS

Method 1: LEFT JOIN + IS NULL

SELECT p.page_id
FROM pages p
LEFT JOIN page_likes pl ON p.page_id = pl.page_id
WHERE pl.page_id IS NULL;

Method 2: NOT EXISTS (often faster)

SELECT page_id FROM pages p
WHERE NOT EXISTS (
  SELECT 1 FROM page_likes pl WHERE pl.page_id = p.page_id
);

EXISTS vs IN

EXISTS — checks if any row exists, stops at first match. Efficient for correlated subqueries. IN — checks against a list. Simpler for small static lists.

Rule of thumb: EXISTS for large tables with correlated subqueries. IN for small, static value lists.

Correlated subquery (know this term) A subquery that references the outer query, so it re-executes per row:

-- Employees earning above their department average
SELECT name, salary, department
FROM employees e
WHERE salary > (
  SELECT AVG(salary) FROM employees WHERE department = e.department
);