Filtering: Advanced

Check if related records exist for each row

EXISTS answers relational questions by checking whether related rows exist in another table.

EXISTS evaluates whether a subquery returns at least one row. Because the subquery can reference the outer query, it is perfect for asking relational questions such as "does this order have a recent payment?" or "has this user submitted any tickets?"

The correlated WHERE clause reuses the outer order_id, so each order performs its own lookup. As soon as the subquery finds a qualifying payment row it stops scanning and returns true, making EXISTS efficient even on large datasets.

EXISTS is optimized for performance because it stops searching as soon as it finds a single matching row.

Now try writing an EXISTS subquery to check for related payment records.

SELECT
  u.user_id
FROM users AS u
WHERE ___ (
  SELECT
    1
  FROM push_notifs AS p
  WHERE ___ = u.user_id
  AND p.status = ___
)

EXISTS is one of the most powerful filtering tools in SQL because it checks relationships without retrieving any column data.

When you only need to know whether related rows exist rather than what those rows contain, EXISTS gives you both correctness and performance.

In production systems, EXISTS-based queries often outperform equivalent JOIN-based approaches because the subquery can short-circuit as soon as the first matching row is found.

Find records with no matching partner

NOT EXISTS is the safest way to find rows that lack related data in another table.

NOT EXISTS flips the logic to keep outer rows that do not find a match inside the subquery. This is the safest pattern for enforcing "no related rows" rules without being tripped up by NULL handling.

Here each customer keeps its spot only when no open ticket rows exist for the same customer_id. Because EXISTS ignores column values, you can add more predicates (date ranges, severities) without changing the correlation pattern.

NOT EXISTS excels at detecting missing data, gaps in processes, and orphaned records.

Missing child rows

Detect unpaid invoices, onboarding gaps, or stale enrollments

NULL-safe by design

Guarantees correct behavior even when the subquery returns NULL values

Self-documenting intent

Outer alias references make the relationship logic obvious to readers

Practice using NOT IN to exclude specific categories from a product listing.

SELECT
  name
FROM products
WHERE category ___ ___ (
  'archived',
  'discontinued'
  )

NOT EXISTS is the safest anti-join pattern because it handles NULL values correctly by design, unlike NOT IN which can silently return zero rows when the subquery contains a NULL.

Choosing between NOT IN and NOT EXISTS is one of the most common correctness decisions in SQL. When in doubt, prefer NOT EXISTS for its predictable NULL behavior.

The pattern of correlated subqueries with EXISTS and NOT EXISTS scales well because modern query optimizers convert them into efficient hash or merge anti-join operations.

Avoid the NULL trap in exclusion filters

NOT IN has a dangerous quirk: a single NULL in the subquery can silently eliminate all results.

NOT IN is concise for enumerating disallowed values, but remember that a single NULL inside the list makes the entire comparison UNKNOWN and filters out every row. The fix is to filter NULLs inside the subquery (or use NOT EXISTS).

Both exclude rows, but they handle NULLs very differently. Choosing the right one prevents silent bugs.

Which NOT IN query is safe when the subquery might contain NULLs?

-- Goal: find transactions with no merchant record
SELECT txn_id, merchant_id, amount
FROM transactions t
WHERE ??? -- your approach here

NOT EXISTS is the safest anti-join pattern because it handles NULL values correctly by design, unlike NOT IN which can silently return zero rows when the subquery contains a NULL.

Choosing between NOT IN and NOT EXISTS is one of the most common correctness decisions in SQL. When in doubt, prefer NOT EXISTS for its predictable NULL behavior.

The pattern of correlated subqueries with EXISTS and NOT EXISTS scales well because modern query optimizers convert them into efficient hash or merge anti-join operations.

Match text with regular expressions

When LIKE wildcards are not expressive enough, regular expressions provide precise pattern matching.

When LIKE patterns become brittle, regex functions provide far more control. Most analytic warehouses expose REGEXP or REGEXP_LIKE, which evaluate a full regular expression against each value. Use them sparingly because they are powerful but harder to read.

In SQL analytics, regex fills the gap between simple LIKE wildcards and procedural code. When you need to validate email formats, extract substrings, enforce ID conventions, or detect patterns across millions of rows, regex is the right tool.

SELECT
  sku,
  name
FROM products
WHERE ___(sku, ___)

When you find yourself adding more OR clauses to a LIKE condition to cover additional cases, that is a reliable signal that REGEXP_LIKE will produce cleaner, more maintainable code.

Build reusable text validation rules

You combine them into a pattern string that REGEXP_LIKE evaluates against each row. If the pattern matches the cell value, the function returns true and the row passes the WHERE clause; otherwise it returns false and the row is filtered out.

SELECT
  ___
FROM users
WHERE ___(
  email,
  '^[a-zA-Z0-9.]+@[a-zA-Z0-9]+\\.[a-z]{2,}$'
  )

When a LIKE pattern with multiple OR conditions becomes hard to read, switching to REGEXP_LIKE usually produces cleaner code.