SQL Predicates — Applied LIKE, BETWEEN, and ANY

QUESTION 6

LIKE Predicate × EXISTS Predicate — Practical search combining pattern matching and existence checks

LIKE PredicateEXISTSPattern MatchingUser Search

Background ▼

The LIKE predicate checks whether a string matches a pattern. It has two wildcards: % (any string of zero or more characters) and _ (any single character). Combined with EXISTS, it enables practical compound filters such as “name matches a pattern × has a purchase history.”

-- % : any string of zero or more characters
WHERE name LIKE 'T%'    -- starts with 'T' (prefix match) — index usable
WHERE name LIKE '%T%'   -- contains 'T' (infix match) — index not usable
-- _ : any single character
WHERE code LIKE 'A_01'   -- A?01 format (? is any one character)
-- ILIKE: case-insensitive (PostgreSQL-specific)
WHERE email ILIKE '%@example.com'

Only prefix matches ('T%') can use an index: Infix and suffix matches cannot use a B-Tree index and require a full scan. For large datasets, use the pg_trgm extension + a GIN index or full-text search.

Problem ▼

From the users table, retrieve users whose name starts with 'T' (LIKE 'T%') and who have at least one completed order. Return user_id, name, email, ordered by user_id ascending.

Tables used

▸ users

user_id	name	email	plan
1	Tanaka Taro	tanaka@example.com	premium
2	Sato Hanako	sato@example.com	standard
3	Tamura Ichiro	tamura@example.com	free
4	Yamada Jiro	yamada@example.com	premium
5	Ito Saburo	ito@example.com	premium

▸ orders

order_id	user_id	status
101	1	completed
102	2	completed
103	3	completed
104	3	completed
105	4	cancelled
106	5	pending

Expected Output ▼

user_id	name	email
1	Tanaka Taro	tanaka@example.com
3	Tamura Ichiro	tamura@example.com

Model Answer ▼

SELECT
  user_id, name, email
FROM   users u
WHERE  u.name LIKE 'T%'           -- LIKE: starts with 'T' (only prefix matches can use an index)
  AND  EXISTS (                     -- TRUE if the subquery returns even one row (short-circuit evaluation)
    SELECT 1                        -- the value does not matter; only row existence is checked
    FROM   orders o
    WHERE  o.user_id = u.user_id    -- correlated condition referencing the outer u.user_id (required)
      AND  o.status  = 'completed'
  )
ORDER BY user_id;

/*
  Execution order (SQL's logical evaluation order):
  1. FROM users u                 → evaluate all 5 rows one by one
  2. WHERE u.name LIKE 'T%'       → narrow to T-name candidates
  3. AND EXISTS(...)              → keep rows with a completed order
  4. SELECT user_id, name, email  → select 3 columns
  5. ORDER BY user_id             → ascending
  */

Explanation (table transitions & key points) ▼

SELECT
  user_id, name, email
FROM   users u
WHERE  u.name LIKE 'T%'
  AND  EXISTS (
  SELECT 1
  FROM   orders o
  WHERE  o.user_id = u.user_id
    AND  o.status  = 'completed'
)
ORDER BY user_id;

LEGEND ▼

Rows read / loaded

① FROM users ▼

FROM users uThese are all 5 rows in the users table targeted by the main query. LIKE and EXISTS are evaluated in two stages for each row.

1 / 5

user_id	name	email	plan
1	Tanaka Taro	tanaka@example.com	premium
2	Sato Hanako	sato@example.com	standard
3	Tamura Ichiro	tamura@example.com	free
4	Yamada Jiro	yamada@example.com	premium
5	Ito Saburo	ito@example.com	premium

5 rows read

LEARNING POINTS

Two LIKE wildcards: % means any string of zero or more characters, while _ means any single character. 'T%' matches “Taro” and “Tamura,” but not “Yamada,” which starts with 'Y'. '_a%' also matches names whose second character is 'a', such as “Yamada” and “Honda.”

Efficiency from AND short-circuit evaluation: When the left side of AND is FALSE in a WHERE clause, the right side is not evaluated. Putting LIKE first lets the query skip the EXISTS subquery for rows that do not match the pattern. Making the most selective filter the first AND condition is a basic practical performance optimization.

ILIKE (PostgreSQL-specific): ILIKE performs case-insensitive searches. It is useful for email-address searches, but a regular B-Tree index is not effective for ILIKE, so a pg_trgm index is usually needed in production.

ANTI-PATTERNS

Expecting an index to work for infix or suffix matches: LIKE '%T%' and LIKE '%ro' cannot use a B-Tree index and cause a full table scan. For name or body-text searches over large datasets, use the pg_trgm extension + a GIN index or PostgreSQL full-text search (to_tsvector/to_tsquery).

Misusing _ and matching unexpected rows: LIKE 'T_ro' matches only “T + any one character + ro.” It does not match a name whose structure includes a space in the middle, such as “Tamura Ichiro.” For data with a space between given and family names, 'T%ro' or 'T%' is safer.

Practical column: Choosing between name search and full-text search

LIKE is convenient for product-name searches and username filters on e-commerce sites, but always remember that only prefix matches ('T%') can use an index. Enabling PostgreSQL's pg_trgm extension makes infix LIKE indexable with one line: CREATE INDEX ON users USING gin(name gin_trgm_ops); For more advanced full-text search, Elasticsearch or PostgreSQL FTS (tsvector) are options. Understanding predicate behavior makes it possible to discuss exactly where the bottleneck is.

QUESTION 7

BETWEEN Predicate × NOT EXISTS Predicate — Price-band filtering and detecting unsold products

BETWEENNOT EXISTSRange FilteringInventory & Sales Analysis

Background ▼

BETWEEN is a concise predicate for range conditions. a BETWEEN x AND y is equivalent to a >= x AND a <= y, a closed interval that includes both endpoints. Combined with NOT EXISTS, it enables inventory and sales-analysis queries such as “price band × no sales history.”

-- BETWEEN: a closed interval including both endpoints (x, y)
WHERE price BETWEEN 3000 AND 7000   -- equivalent to >= 3000 AND <= 7000

-- NOT BETWEEN: outside the range
WHERE price NOT BETWEEN 3000 AND 7000 -- equivalent to < 3000 OR > 7000

-- Caution for date ranges (timestamp type)
WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31'
-- for timestamp, this reaches only '2024-01-31 00:00:00' → orders later that day are missed
-- correct: created_at >= '2024-01-01' AND created_at < '2024-02-01'

BETWEEN readability: The paired lower and upper bounds are visually clear and communicate intent well. However, date and timestamp types include only midnight at the start of the final day, so >= start AND < end+1 is safer for date ranges.

Problem ▼

From the products table, retrieve products in the 'Accessories' category with price from 3,000 to 7,000 (BETWEEN) that have no completed orders, using NOT EXISTS. Return product_id, name, price, stock, ordered by price ascending.

Tables used

▸ products

product_id	name	category	price	stock
1	Wireless Mouse	Accessories	3500	80
2	Mechanical Keyboard	Accessories	8800	45
3	HDMI Cable 2m	Accessories	1200	150
4	7-Port USB Hub	Accessories	4200	30
5	External SSD 1TB	Storage	9800	20
6	HD Webcam	Accessories	6500	15

▸ orders

order_id	product_id	status
101	4	completed
102	6	pending
103	1	cancelled

Expected Output ▼

product_id	name	price	stock
1	Wireless Mouse	3500	80
6	HD Webcam	6500	15

Model Answer ▼

SELECT
  product_id, name, price, stock
FROM   products p
WHERE  p.category = 'Accessories'             -- ① category filter
  AND  p.price BETWEEN 3000 AND 7000    -- ② price band (closed interval including both endpoints)
  AND  NOT EXISTS (                     -- ③ zero rows from the subquery = no completed orders
    SELECT 1
    FROM   orders o
    WHERE  o.product_id = p.product_id  -- correlation condition with the outer product (required)
      AND  o.status      = 'completed'  -- check completed orders only
  )
ORDER BY p.price;

/*
  Execution order (SQL's logical evaluation order):
  1. FROM products p                        → read 6 rows
  2. WHERE p.category = 'Accessories'      → filter by category
  3. AND p.price BETWEEN ...               → filter by price band
  4. AND NOT EXISTS(...)                    → keep products with no matching order
  5. SELECT product_id, name, price, stock  → select 4 columns
  6. ORDER BY p.price                       → sort by price ascending
  */

Explanation (table transitions & key points) ▼

SELECT
  product_id, name, price, stock
FROM   products p
WHERE  p.category = 'Accessories'
  AND  p.price BETWEEN 3000 AND 7000
  AND  NOT EXISTS (
  SELECT 1
  FROM   orders o
  WHERE  o.product_id = p.product_id
    AND  o.status     = 'completed'
)
ORDER BY p.price;

LEGEND ▼

Rows read / loaded

① FROM products ▼

FROM products pThese are all 6 rows in the products table targeted by the main query. Apply the category → BETWEEN → NOT EXISTS filters in three stages.

1 / 6

product_id	name	category	price	stock
1	Wireless Mouse	Accessories	3500	80
2	Mechanical Keyboard	Accessories	8800	45
3	HDMI Cable 2m	Accessories	1200	150
4	7-Port USB Hub	Accessories	4200	30
5	External SSD 1TB	Storage	9800	20
6	HD Webcam	Accessories	6500	15

6 rows read

LEARNING POINTS

BETWEEN includes both endpoints: price BETWEEN 3000 AND 7000 is exactly equivalent to price >= 3000 AND price <= 7000. Both 3000 and 7000 are included. It is readable and makes the intent clear, but it is important to understand the equivalent expansion.

The timestamp trap with date BETWEEN: For a timestamp, created_at BETWEEN '2024-01-01' AND '2024-01-31' includes only through 2024-01-31 00:00:00, so orders later on January 31 are missed. For date ranges, use >= '2024-01-01' AND < '2024-02-01'.

Multi-stage filtering and index strategy: Evaluating category (equality) → price BETWEEN (range) → NOT EXISTS (correlated subquery) reduces the row count efficiently. Make a habit of checking the execution plan with EXPLAIN ANALYZE and confirming that each filter can use an Index Scan.

ANTI-PATTERNS

Confusing NOT BETWEEN with an AND condition: NOT BETWEEN 3000 AND 7000 means < 3000 OR > 7000. A common mistake is to turn NOT (price >= 3000 AND price <= 7000) into price < 3000 AND price > 7000, which can never be TRUE. Remember that De Morgan's law changes AND to OR.

The code-order trap with string BETWEEN: status BETWEEN 'a' AND 'c' performs a dictionary-order comparison that depends on the collation. For string ranges, IN or OR is clearer and safer.

Practical column: Inventory, price bands, and BETWEEN

Dashboards for e-commerce and SaaS frequently need to find “products in this price band that are in stock and have not sold even once,” either in scheduled batches or admin screens. The combination of BETWEEN + NOT EXISTS concisely expresses the practical pattern “within a range × no record.” It applies broadly to inventory management, dead-stock detection, and selecting sale items.

QUESTION 8

IS NULL Predicate × IN Subquery — NULL-safe filtering and dynamic-list filtering

IS NULLIN SubqueriesNULL SafetyThree-Valued Logic

Background ▼

In SQL's three-valued logic (TRUE / FALSE / UNKNOWN), every comparison with NULL returns UNKNOWN. In a WHERE clause, UNKNOWN excludes a row just like FALSE. To test whether a value is NULL, using IS NULL / IS NOT NULL is essential.

-- ✗ = NULL always produces UNKNOWN (never use it)
WHERE shipped_at = NULL     -- always UNKNOWN → all rows excluded (the opposite of the intent)
WHERE shipped_at <> NULL    -- always UNKNOWN → all rows excluded

-- ✓ IS NULL / IS NOT NULL are the only correct forms
WHERE shipped_at IS NULL     -- keep only NULL rows (find unshipped orders)
WHERE shipped_at IS NOT NULL -- keep only non-NULL rows (shipped orders)

-- NULL propagates through expressions
SELECT amount + discount_amount         -- discount is NULL → the result is also NULL
SELECT COALESCE(discount_amount, 0)   -- replace NULL with a fallback value using COALESCE

The essence of three-valued logic: NULL = NULL evaluates to UNKNOWN, not TRUE. NULL values are not considered equal to one another. In PostgreSQL, NULL IS NOT DISTINCT FROM NULL compares two NULLs as equal.

Problem ▼

From the orders table, retrieve orders where status is 'processing' and shipped_at is NULL (unshipped), limited to orders from users whose plan is 'premium'. Use an IN subquery. Return order_id, user_id, amount, status, ordered by amount descending.

Tables used

▸ orders

order_id	user_id	amount	status	shipped_at
101	1	8000	completed	2024-01-15
102	2	5000	processing	NULL
103	1	12000	processing	NULL
104	3	3000	completed	2024-01-20
105	4	9500	processing	2024-01-25
106	3	7000	processing	NULL

▸ users

user_id	name	plan
1	Tanaka Taro	premium
2	Sato Hanako	premium
3	Ichiro Suzuki	standard
4	Yamada Jiro	free

Expected Output ▼

order_id	user_id	amount	status
103	1	12000	processing
102	2	5000	processing

Model Answer ▼

SELECT
  o.order_id, o.user_id, o.amount, o.status
FROM   orders o
WHERE  o.status     =  'processing'       -- ① processing orders only
  AND  o.shipped_at IS NULL              -- ② unshipped (= NULL does not work!)
  AND  o.user_id    IN (                 -- ③ restrict to orders from premium users
    SELECT user_id                       -- must return exactly one column
    FROM   users
    WHERE  plan = 'premium'              -- dynamically generate the premium user_id list
  )
ORDER BY o.amount DESC;

/*
  Execution order (SQL's logical evaluation order):
  1. Evaluate the subquery first                 → generate the IN list of premium users
  2. FROM orders o                               → read 6 rows
  3. WHERE o.status = 'processing'              → keep processing orders
  4. AND o.shipped_at IS NULL                   → keep unshipped orders
  5. AND o.user_id IN (...)                     → keep matching users
  6. SELECT order_id, user_id, ...              → select 4 columns
  7. ORDER BY o.amount DESC                     → sort by amount descending
  */

Explanation (table transitions & key points) ▼

SELECT
  o.order_id, o.user_id, o.amount, o.status
FROM   orders o
WHERE  o.status     = 'processing'
  AND  o.shipped_at IS NULL
  AND  o.user_id    IN (
  SELECT user_id
  FROM   users
  WHERE  plan = 'premium'
)
ORDER BY o.amount DESC;

LEGEND ▼

Rows read / loaded

① FROM orders ▼

FROM orders oThese are all 6 rows in the orders table targeted by the main query. Rows with NULL shipped_at (unshipped) are mixed with rows that have a shipping date (shipped).

1 / 7

order_id	user_id	amount	status	shipped_at
101	1	8000	completed	2024-01-15
102	2	5000	processing	NULL
103	1	12000	processing	NULL
104	3	3000	completed	2024-01-20
105	4	9500	processing	2024-01-25
106	3	7000	processing	NULL

6 rows read

LEARNING POINTS

Never use = NULL: In SQL, NULL = NULL evaluates to UNKNOWN, not TRUE. Since WHERE treats UNKNOWN as excluded, WHERE shipped_at = NULL excludes every row. Only IS NULL / IS NOT NULL can test NULL values. This remains one of the most important sources of production bugs even after it is learned.

NULL propagates through expressions: In arithmetic such as amount + discount_amount, if either term is NULL, the entire result is NULL. A standard practical pattern is to replace NULL with a fallback using COALESCE(discount_amount, 0) before calculating.

IS DISTINCT FROM (NULL-safe equality): In PostgreSQL, a IS NOT DISTINCT FROM b is equivalent to a = b OR (a IS NULL AND b IS NULL). It is useful for detecting changes in UPDATE operations, such as skipping rows whose values have not changed. This is an important pattern when comparing nullable columns.

ANTI-PATTERNS

shipped_at = NULL (the most common NULL bug): WHERE shipped_at = NULL evaluates to UNKNOWN for every row and returns zero rows. When you see “zero rows even though the data exists,” check for IS NULL / IS NOT NULL. This bug can also appear in queries generated by an ORM.

Confusing how COUNT treats NULL: COUNT(shipped_at) excludes NULL values, whereas COUNT(*) counts every row including NULLs. If you want to count shipped orders but use COUNT(*), unshipped orders are included. The correct column-specific form is COUNT(shipped_at).

Practical column: NULL design and when to use COALESCE

Designs that give NULL a meaning, such as “shipped_at is NULL = unshipped,” are common in practice, but using NULL for both “no value” and a semantic state can be confusing. If you use shipped_at IS NULL frequently, a separate shipped_status column managed as an enum can make queries clearer. In SELECT expressions, converting NULL for display with a pattern such as COALESCE(note, '—') is also a practical standard.

QUESTION 9

ANY / ALL Predicates — Comparing with some or all values (competitive price analysis)

ANY PredicateALL PredicateMulti-Value ComparisonCompetitive Analysis

Background ▼

The ANY predicate (also written SOME) returns TRUE when the comparison is TRUE for at least one value from the subquery. The ALL predicate returns TRUE only when the comparison is TRUE for every value from the subquery.

-- ANY: TRUE if the comparison is TRUE for one value → equivalent to a chain of ORs
WHERE price < ANY (SELECT price FROM competitor)
-- ↑ equivalent: WHERE price < (SELECT MAX(price) FROM competitor)

-- ALL: TRUE if the comparison is TRUE for every value → equivalent to a chain of ANDs
WHERE price > ALL (SELECT price FROM competitor)
-- ↑ equivalent: WHERE price > (SELECT MAX(price) FROM competitor)

-- Special equivalences
WHERE col =  ANY (list)  -- equivalent to = IN (list)
WHERE col <> ALL (list)  -- equivalent to = NOT IN (list) (NULL trap applies)

The ALL + NULL trap: If an ALL list contains NULL, val <> NULL becomes UNKNOWN and the entire ALL result becomes FALSE. Since <> ALL (the equivalent of NOT IN) can exclude every row when NULL is present, prefer NOT EXISTS in production.

Problem ▼

From the products table (our products), retrieve products that are cheaper than at least one same-category competitor product in the competitor_prices table, using < ANY. Return product_id, name, price, ordered by price ascending. (The explanation tab also shows the ALL variant.)

Tables used

▸ products (our products)

product_id	name	category	price
1	Model A Headphones	Audio	12000
2	Model B Headphones	Audio	8500
3	Model C Headphones	Audio	15000
4	Model D Headphones	Audio	6000

▸ competitor_prices (competitors)

comp_id	name	category	price
1	Competitor X Headphones	Audio	9000
2	Competitor Y Headphones	Audio	11000
3	Competitor Z Headphones	Audio	13500

Expected Output ▼

product_id	name	price
4	Model D Headphones	6000
2	Model B Headphones	8500
1	Model A Headphones	12000

Model Answer ▼

-- ① ANY: products cheaper than at least one competitor (below the competitor maximum)
SELECT product_id, name, price
FROM   products p
WHERE  p.category = 'Audio'
  AND  p.price < ANY (           -- TRUE if cheaper than at least one value
    SELECT price                 -- ↑ equivalent to less than MAX(price)
    FROM   competitor_prices c
    WHERE  c.category = 'Audio'
  )
ORDER BY p.price;

/*
  Execution order (ANY case):
  1. Evaluate the subquery first             → generate the competitor price list
  2. FROM products p                         → read 4 rows
  3. WHERE p.category = 'Audio'              → filter by category
  4. AND p.price < ANY (...)                 → keep products cheaper than one competitor
  5. SELECT + ORDER BY p.price               → output in price ascending order
  */

Explanation (table transitions & key points) ▼

SELECT product_id, name, price
FROM   products p
WHERE  p.category = 'Audio'
  AND  p.price < ANY (
  SELECT price
  FROM   competitor_prices c
  WHERE  c.category = 'Audio'
)
ORDER BY p.price;

LEGEND ▼

Rows read / loaded

① FROM products (our products) ▼

FROM products p WHERE p.category = 'Audio'This is the table of our products targeted by the main query. Evaluate ANY for the 4 products in the Audio category.

1 / 5

product_id	name	price
1	Model A Headphones	12000
2	Model B Headphones	8500
3	Model C Headphones	15000
4	Model D Headphones	6000

4 rows read

SELECT product_id, name, price
FROM   products p
WHERE  p.category = 'Audio'
  AND  p.price > ALL (
  SELECT price
  FROM   competitor_prices c
  WHERE  c.category = 'Audio'
)
ORDER BY p.price;

LEGEND ▼

Rows read / loaded

① FROM products (our products) ▼

FROM products p WHERE p.category = 'Audio'This is the ALL variant. Find products that are more expensive than every competitor. p.price > ALL is equivalent to p.price > MAX(competitor).

1 / 4

product_id	name	price
1	Model A Headphones	12000
2	Model B Headphones	8500
3	Model C Headphones	15000
4	Model D Headphones	6000

4 rows read

LEARNING POINTS

The logic of ANY / ALL: ANY (= SOME) means “one comparison is TRUE → the whole predicate is TRUE” (equivalent to a chain of ORs), while ALL means “every comparison is TRUE → the whole predicate is TRUE” (equivalent to a chain of ANDs). p.price < ANY(list) is equivalent to p.price < MAX(list), and p.price > ALL(list) is equivalent to p.price > MAX(list).

= ANY is equivalent to IN: col = ANY (1, 2, 3) produces the same result as col IN (1, 2, 3). IN is generally more readable for equality comparisons, so it is the common practical choice. <> ALL is equivalent to NOT IN, but prefer NOT EXISTS because of the NULL trap.

When the subquery is empty (zero rows): If an ANY subquery returns zero rows, ANY is FALSE because there is no value to compare. If an ALL subquery returns zero rows, ALL is TRUE (all comparisons are vacuously true). Remember this asymmetric behavior.

ANTI-PATTERNS

NULL contamination in <> ALL (= NOT IN): If an ALL list contains NULL, val <> NULL becomes UNKNOWN and the entire result becomes FALSE. Thus <> ALL has the same NULL trap as NOT IN. NOT EXISTS is unaffected by NULL.

Returning multiple columns from an ANY / ALL subquery: An ANY or ALL subquery must return exactly one column. Returning multiple columns causes a syntax error, just as it does with IN.

Practical column: Competitive price analysis and rewriting to a scalar subquery

ANY / ALL are somewhat unintuitive from a readability perspective, so in practice they are often rewritten as scalar subqueries using MAX / MIN. For example, price < ANY (SELECT price FROM competitor) is equivalent to price < (SELECT MAX(price) FROM competitor), and the latter may make the intent clearer. For complex conditions such as “cheaper than any competitor meeting a particular condition,” ANY can be more concise. Being able to read both forms is important in practice.

QUESTION 10

Compound Multiple EXISTS — Precise row selection with AND EXISTS + NOT EXISTS

Multiple EXISTSAND EXISTS + NOT EXISTSPractical SynthesisUser Behavior Analysis

Background ▼

Combining multiple EXISTS / NOT EXISTS predicates with AND enables precise row selection: a row satisfying condition A exists, while no row satisfying condition B exists. This is one of the most common patterns in practical behavior analysis and segment extraction.

-- Combine multiple EXISTS predicates with AND
WHERE EXISTS (               -- ① a row satisfying condition A exists
  SELECT 1 FROM orders o1
  WHERE  o1.user_id = u.user_id
    AND  o1.status = 'completed'
)
AND NOT EXISTS (             -- ② no row satisfying condition B exists
  SELECT 1 FROM orders o2
  WHERE  o2.user_id = u.user_id
    AND  o2.status = 'cancelled'
)

The power of multiple EXISTS: Complex behavior patterns such as “a valuable user with completed orders but no cancelled orders” can be expressed simply by adding subqueries. Conditions that are difficult to express with JOIN + aggregation can be written intuitively by combining EXISTS predicates.

Problem ▼

From the users table, retrieve users on the 'premium' or 'standard' plan who have at least one completed order and no cancelled orders. Return user_id, name, plan, ordered by user_id ascending.

Tables used

▸ users

user_id	name	plan
1	Tanaka Taro	premium
2	Sato Hanako	standard
3	Ichiro Suzuki	premium
4	Yamada Jiro	free
5	Ito Saburo	standard

▸ orders

order_id	user_id	status
101	1	completed
102	1	cancelled
103	2	completed
104	3	completed
105	3	pending
106	5	pending

Expected Output ▼

user_id	name	plan
2	Sato Hanako	standard
3	Ichiro Suzuki	premium

Model Answer ▼

SELECT
  user_id, name, plan
FROM   users u
WHERE  u.plan IN ('premium', 'standard')  -- ① target plans only
  AND  EXISTS (                           -- ② at least one completed order
    SELECT 1
    FROM   orders o1
    WHERE  o1.user_id = u.user_id         -- correlation condition with the outer query
      AND  o1.status  = 'completed'
  )
  AND  NOT EXISTS (                       -- ③ no cancelled orders
    SELECT 1
    FROM   orders o2                      -- reference the same table with a different alias
    WHERE  o2.user_id = u.user_id         -- correlation condition with the outer query
      AND  o2.status  = 'cancelled'
  )
ORDER BY user_id;

/*
  Execution order (SQL's logical evaluation order):
  1. FROM users u                     → evaluate all 5 rows one by one
  2. WHERE u.plan IN (...)            → filter by plan
  3. AND EXISTS(...completed...)      → keep users with a completed order
  4. AND NOT EXISTS(...cancelled...)  → keep users with no cancelled order
  5. SELECT user_id, name, plan       → select 3 columns
  6. ORDER BY user_id                 → ascending
  */

Explanation (table transitions & key points) ▼

SELECT
  user_id, name, plan
FROM   users u
WHERE  u.plan IN ('premium', 'standard')
  AND  EXISTS (
  SELECT 1
  FROM   orders o1
  WHERE  o1.user_id = u.user_id
    AND  o1.status  = 'completed'
)
  AND  NOT EXISTS (
  SELECT 1
  FROM   orders o2
  WHERE  o2.user_id = u.user_id
    AND  o2.status  = 'cancelled'
)
ORDER BY user_id;

LEGEND ▼

Rows read / loaded

① FROM users ▼

FROM users uThese are all 5 rows in the users table targeted by the main query. Apply IN → EXISTS → NOT EXISTS in three stages.

1 / 6

user_id	name	plan
1	Tanaka Taro	premium
2	Sato Hanako	standard
3	Ichiro Suzuki	premium
4	Yamada Jiro	free
5	Ito Saburo	standard

5 rows read

LEARNING POINTS

Referencing one table with multiple EXISTS predicates: The orders table is referenced twice with different aliases: o1 for the completed-order check and o2 for the cancelled-order check. Changing the aliases lets you write independent correlated subqueries against the same table. Whereas multiple aggregate conditions can be difficult to express in one JOIN, chaining EXISTS is intuitive.

Practical uses for EXISTS + NOT EXISTS: Segment extraction such as “users on a paid plan who purchased in the last 30 days and have never cancelled” is common in marketing campaigns. You can precisely control the conditions simply by stacking predicates such as AND EXISTS (time-window subquery) AND NOT EXISTS (cancellation subquery).

Short-circuit evaluation and performance: AND conditions are evaluated from left to right, and later conditions are skipped once FALSE is determined. Putting the most selective condition first reduces the cost of later EXISTS evaluations. In practice, it is recommended to put an indexable IN filter first.

ANTI-PATTERNS

Using the same alias for two references to the same table: If both subqueries use the same alias, as in EXISTS (...FROM orders o ...) AND NOT EXISTS (...FROM orders o ...), scope confusion and bugs can result. Give each subquery a unique alias (o1, o2).

Using COUNT instead of EXISTS: WHERE (SELECT COUNT(*) FROM orders WHERE user_id=u.user_id AND status='completed') > 0 works, but it counts every row before comparing and is inefficient. EXISTS stops as soon as it finds one row (short-circuit evaluation), so always use EXISTS for existence checks.

Practical column: User segmentation and stacking AND EXISTS predicates

Behavioral segments with multiple conditions, such as “purchased in the last 30 days × no past cancellations × has bought from a specific category,” are routine in CRM and marketing. Designing the query by stacking AND EXISTS / AND NOT EXISTS has the advantage that each condition can be added or removed as an independent EXISTS clause, which improves maintainability. However, many EXISTS clauses can make the execution plan heavy, so inspect the bottleneck with EXPLAIN ANALYZE and add indexes as needed.