Filtering: Advanced

Subqueries, regex, and other crimes

Subqueries, regex, and other crimes

Category

SQL

Difficulty

advanced

Duration

19 minutes

Challenges

0 hands-on challenges

Topics covered: Correlated subqueries (EXISTS), NOT EXISTS for missing rows, NOT IN vs NULL gotchas, REGEXP_LIKE patterns, Regex operators and patterns

Lesson Sections

1. Correlated subqueries (EXISTS) (concepts: sqlExists)

   Watch how each order probes the subquery: the outer row expands, the payments table responds, and only rows with a qualifying payment move into the result. Each outer row probes the subquery independently. As soon as a qualifying payment is found, the subquery stops scanning and the order passes through. Orders with no matching payment are excluded entirely. EXISTS Efficiency Which subquery approach checks for related rows more efficiently?

2. NOT EXISTS for missing rows

   Common Use Cases Which query correctly finds customers with no open tickets?

3. NOT IN vs NULL gotchas

   NOT IN vs NOT EXISTS

4. REGEXP_LIKE patterns (concepts: sqlRegexMatch)

   This expression keeps webinar signups whose email domains are orbit.ai or apollo.ai. Regex anchors (^ and $) guarantee full-string matches so you do not pull in typos like orbit.ai.fake. Regex Fundamentals Understanding regex concepts helps you write precise patterns that match exactly what you intend. What is Regex? The core idea is pattern matching: instead of searching for a literal string, you describe the shape of what you want. A regex pattern is a mini-program that the engine executes cha

5. Regex operators and patterns

   Each regex operator has a specific purpose in constructing patterns that match text. Regex operators are building blocks that describe what characters to match and how many times. They do not filter or return text on their own. Start with simple regex patterns using anchors and character classes before adding quantifiers and groups.