Filtering: Advanced

EXISTS takes a subquery and asks a single yes-or-no question about it: does this subquery return at least 1 row? The answer is TRUE as soon as a row turns up and FALSE when none do. It never reads the values in those rows, so putting SELECT 1 inside it gives the same result as SELECT * or SELECT order_id. Writing SELECT 1 is the convention because it tells the next reader that only existence matters.

Maple Mugs and North Knits survive, and so does Tidal Tees. Order 105 carries a NULL store_id, and o.store_id = s.store_id is never TRUE when one side is NULL, so that order matches no store and changes nothing here. Keep order 105 in mind: in the NOT IN section later in this lesson, the same row empties an entire result.

Every condition you add to the subquery's WHERE narrows what counts as a related row. Adding AND o.status = 'paid' turns the question into: does this store have a paid order? Tidal Tees drops out, because its only order, 104, was refunded. Maple Mugs stays: order 102 was refunded, but order 101 is paid, and 1 paid row is enough.

The link can also combine several columns and comparisons. On a schema with launch dates, o.store_id = s.store_id AND o.created_at >= s.launched_at asks whether a store took an order after it launched. IN compares the outer column to the subquery's output by equality only, so a >= has to sit inside the subquery's own WHERE. EXISTS keeps both the equality and the >= in one WHERE that reads both tables.

NOT EXISTS flips the answer: it is TRUE when the subquery returns 0 rows for the current outer row. That makes it the standard tool for orphan hunting, finding rows on one side with no partner on the other. Stores with no orders, users who never logged in, invoices with no payment and products nobody reviewed all share this query shape.

The status check belongs inside the subquery. There it redefines which partner rows count, so a store whose only order was refunded has no paid partner and stays. A condition in the outer WHERE filters the stores themselves, and stores has no status column to test. Each of the 3 tabs below asks a different question of the same 2 tables, which this section aliases as st and od.

You already know a set-based way to find missing ids: SELECT store_id FROM stores EXCEPT SELECT store_id FROM orders returns 2 and 4. It answers the same question, with 3 differences that matter in practice.

x NOT IN (a, b, c) is shorthand for x <> a AND x <> b AND x <> c. The subquery here returns 1, 1, 3 and 5, plus a NULL from order 105, so for store 2 the filter becomes 2 <> 1 AND 2 <> 1 AND 2 <> 3 AND 2 <> 5 AND 2 <> NULL. Any comparison with NULL yields NULL, which means unknown. An AND that holds a NULL and no FALSE is NULL as a whole. WHERE keeps a row only when its condition is TRUE, so store 2 is dropped. The table runs the same check for all 5 stores.

For store 2, the NOT EXISTS subquery tests each order in turn: 1 = 2 is FALSE, 1 = 2 is FALSE, 3 = 2 is FALSE, 5 = 2 is FALSE and NULL = 2 is NULL. The inner WHERE also keeps only TRUE rows, so the subquery returns nothing and NOT EXISTS is TRUE. An unknown comparison can never make a subquery nonempty, so the NULL order has no effect. IN is safe for the opposite reason: it expands to comparisons joined by OR, and once a value matches, the OR is TRUE whatever else the list holds.

LIKE knows 2 wildcards, % for any run of characters and _ for exactly 1, and its pattern must cover the whole string. That handles prefix and suffix checks. It cannot ask for exactly 4 digits, and it has no way to express a letter-or-digit choice or 1 or more of something. Regular expressions can, and REGEXP_LIKE(text, pattern) returns TRUE when the pattern matches the text.

In this lesson's SQL a backslash inside a string literal reaches the regex engine as typed, so '\d' arrives as \d and means a digit.

One difference catches people coming from LIKE: REGEXP_LIKE matches anywhere in the string. REGEXP_LIKE('SUMMER25', '25') is TRUE because 25 appears somewhere inside it. To demand that a pattern covers the whole value, anchor it with ^ for the start and $ for the end.

Merchants type discount codes into a free-text field, so the 6 codes below follow no single format. The animation tests each against ^[A-Z]+\d+$: uppercase letters followed by digits, at least 1 of each, with nothing else in the string.

Only SUMMER25 passes. summer25 fails at its first character, because [A-Z] covers uppercase only. FREESHIP has letters but no digit, and \d+ needs at least 1. VIP 10 has a space between its letters and digits that no part of the pattern allows, and both BF codes stop at their dashes.

Leave out the $ and the rule weakens without changing any of these rows: ^BF-\d{4}-\d{2} also accepts BF-2024-405, because the first 2 digits after the second dash satisfy \d{2} and nothing checks what follows. An unanchored pattern only checks that a matching piece exists somewhere in the value.

tidal tees@shop.io stops at the first WHEN. hello@fernstudio has no dot after its @, so the group (\.[a-z]+)+ has nothing to repeat. Pixel.Prints@gmail.com passes the domain rule because that rule carries (?i). The uppercase rule lacks it, so the address trips there. That label marks cleanup work rather than a delivery failure: the address still receives mail, and the row goes to a normalization step. Stores 1 and 3 fall through to ELSE.

Stores 2 and 4 pass the NOT EXISTS. hello@fernstudio fails the pattern, so only Pixel Prints gets the email. Inside a character class, . is literal wherever it sits and needs no backslash, which is why [a-z0-9._] accepts the dot in Pixel.Prints. A - between 2 characters forms a range, as in a-z, so it is literal only as the first or last character of the class, which is why [a-z0-9-] puts it last.