Predicates — Learn EXISTS, IN subqueries, ANY/ALL, and IS DISTINCT FROM in Practice

QUESTION 1

EXISTS Predicate — Check whether even one matching row exists with a correlated subquery

EXISTSCorrelated SubqueryExistence CheckSemi-Join

Background ▼

The EXISTS predicate returns TRUE when a subquery returns at least one row, and FALSE when it returns zero rows. It is typically combined with a correlated subquery, which brings columns from the outer query into the inner subquery.

WHERE EXISTS (
  SELECT 1               -- The SELECT value is not evaluated (1, *, and NULL all behave the same)
  FROM   orders o
  WHERE  o.user_id = u.user_id  -- Correlation condition: bring outer u into the inner query
    AND  o.status  = 'completed'
)

Short-circuit evaluation: EXISTS immediately returns TRUE and stops when it finds one matching row. It is much more efficient than scanning every row with COUNT(*) > 0.

Problem ▼

From the users table, retrieve users who have at least one completed order. Return user_id, name, plan, ordered by user_id ascending.

Tables used

▸ users

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

▸ orders

order_id	user_id	amount	status	ordered_at
101	1	15000	completed	2024-05-01
102	1	8000	cancelled	2024-05-10
103	2	5000	pending	2024-05-15
104	3	22000	completed	2024-05-20
105	4	3000	cancelled	2024-05-22
106	3	9000	completed	2024-05-25
107	5	12000	completed	2024-05-28

Expected Output ▼

Expected output (user_id ascending):

user_id	name	plan
1	Taro Tanaka	premium
3	Ichiro Suzuki	premium
5	Saburo Takahashi	free

Note: user 2 has only a pending order, and user 4 only a cancelled order → no completed order → excluded.

Model Answer ▼

SELECT
  u.user_id, u.name, u.plan
FROM   users u
WHERE  EXISTS (
  SELECT 1                        -- SELECT *, 'x', and NULL behave the same
  FROM   orders o
  WHERE  o.user_id = u.user_id   -- Correlation condition: bring outer u into the inner query
    AND  o.status  = 'completed'
)
ORDER BY u.user_id;

/*
  Execution order (SQL's logical evaluation order):
  1. FROM users u                      → scan the 5 outer rows
  2. [For each row] correlated EXISTS subquery → short-circuit on a completed order
  3. WHERE EXISTS (...)                → keep rows where the result is TRUE
  4. SELECT u.user_id, u.name, u.plan  → select 3 columns
  5. ORDER BY u.user_id                → sort by user_id ascending
  */

Explanation (table transitions & key points) ▼

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

LEGEND ▼

Rows read / loaded

① FROM users u ▼

FROM users uRead all 5 rows from users as the outer table. The correlated EXISTS subquery is then evaluated separately for each user row.

1 / 4

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

Outer table: 5 rows read

LEARNING POINTS

Short-circuit evaluation: EXISTS returns TRUE and stops as soon as the inner subquery finds one row. This is much more efficient than scanning every row and counting the result with COUNT(*) > 0.

The SELECT value can be anything: The value in the SELECT clause of EXISTS (SELECT 1 ...) is never evaluated. SELECT *, SELECT NULL, and SELECT 'x' behave exactly the same; SELECT 1 is the most common convention.

Role of the correlation condition: WHERE o.user_id = u.user_id brings an outer-table column into the inner subquery. Without it, the inner query scans all rows and is always TRUE, causing every outer row to be returned.

EXISTS vs IN：For a non-correlated subquery, user_id IN (SELECT user_id FROM ...) and EXISTS are equivalent. Modern optimizers often convert both to similar plans; when a correlation condition is present, EXISTS is usually the more natural expression (see Q3).

ANTI-PATTERNS

EXISTS(SELECT COUNT(*) ...) > 0:EXISTS does not return a row count. Counting inside the subquery and then comparing it is redundant, and it scans every row even after finding the first match.Writing WHERE EXISTS (...) is sufficient.

Forgetting the correlation condition:WHERE EXISTS (SELECT 1 FROM orders WHERE status = 'completed') returns every user whenever the orders table contains even one completed order. Always add a correlation condition such as o.user_id = u.user_id.

Practical column: EXISTS vs. semi-joins

EXISTS is typically executed internally as a semi-join.INNER JOIN does not multiply rows: if one user has multiple orders, a JOIN returns that user multiple times and requires DISTINCT. EXISTS removes such duplicates automatically, so it is usually more appropriate than JOIN when retrieving “users who have ...” or “rows for which ... exists.”

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.

QUESTION 2

NOT EXISTS Predicate — The anti-join pattern and the NOT IN NULL trap

NOT EXISTSAnti-JoinAbsence CheckNOT IN Warning

Background ▼

NOT EXISTS returns rows for which no matching row exists. It is the anti-join pattern, the counterpart to INNER JOIN.

WHERE NOT EXISTS (
  SELECT 1
  FROM   order_items oi
  WHERE  oi.product_id = p.product_id  -- FALSE if even one match exists; TRUE if none exists
)

NOT IN + NULL = zero rows (critical bug): If a NOT IN list contains even one NULL, x <> NULL is UNKNOWN, so the whole AND expression is UNKNOWN → every row is excluded. NOT EXISTS avoids this trap.

Problem ▼

From the products table, retrieve products that have never been ordered. Use NOT EXISTS to check order_items, return product_id, name, category, price, and order by product_id ascending.

Tables used

▸ products

product_id	name	category	price
1	Smartphone X	Smartphone	89800
2	Smartbook Pro	PC	128000
3	Tablet Air	Tablet	64800
4	Wireless Earbuds	Accessories	12800
5	USB Cable	Accessories	980
6	Gaming PC Pro	PC	198000

▸ order_items

item_id	order_id	product_id	quantity
1	101	1	1
2	101	4	2
3	104	2	1
4	106	3	1
5	107	1	1
6	105	NULL	1

Note: item 6 references a deleted product → product_id = NULL.

Expected Output ▼

Expected output (product_id ascending):

product_id	name	category	price
5	USB Cable	Accessories	980
6	Gaming PC Pro	PC	198000

Note: products 1, 2, 3, and 4 appear in order_items and are excluded. Products 5 and 6 have never been ordered.

Model Answer ▼

SELECT
  p.product_id, p.name, p.category, p.price
FROM   products p
WHERE  NOT EXISTS (
  SELECT 1
  FROM   order_items oi
  WHERE  oi.product_id = p.product_id  -- Correlation condition: FALSE if even one match exists
)
ORDER BY p.product_id;

-- ✗ Incorrect form (returns zero rows when order_items.product_id contains NULL):
-- WHERE p.product_id NOT IN (SELECT product_id FROM order_items)

/*
  Execution order (SQL's logical evaluation order):
  1. FROM products p                     → outer: scan 6 rows
  2. [For each row] correlated NOT EXISTS subquery:
       FROM order_items oi
       WHERE oi.product_id = p.product_id → one or more matches → FALSE (excluded)
                                            zero matches             → TRUE (passes)
  3. WHERE NOT EXISTS (...)              → keep 2 TRUE rows (products 5 and 6)
  4. SELECT p.product_id, p.name, ...   → select 4 columns
  5. ORDER BY p.product_id              → sort by ID ascending
*/

Explanation (table transitions & key points) ▼

SELECT
  p.product_id, p.name, p.category, p.price
FROM   products p
WHERE  NOT EXISTS (
  SELECT 1
  FROM   order_items oi
  WHERE  oi.product_id = p.product_id
)
ORDER BY p.product_id;

LEGEND ▼

Rows read / loaded

① FROM products p ▼

FROM products pRead all 6 rows from products as the outer table. We will find the products that have never been ordered.

1 / 4

product_id	name	category	price
1	Smartphone X	Smartphone	89800
2	Smartbook Pro	PC	128000
3	Tablet Air	Tablet	64800
4	Wireless Earbuds	Accessories	12800
5	USB Cable	Accessories	980
6	Gaming PC Pro	PC	198000

Outer table: 6 rows read

LEARNING POINTS

NOT EXISTS is the safest anti-join implementation：The pattern that returns rows for which no matching row exists is called an anti-join. NOT EXISTS works correctly even when NULL is present and is the safest option.

NOT IN + NULL = zero rows (critical bug)：x NOT IN (..., NULL) expands to x <> a AND x <> b AND ... AND x <> NULL. x <> NULL is always UNKNOWN → the whole AND expression is UNKNOWN → every row is excluded. This is one of the easiest production bugs to miss.

Three anti-join patterns：①NOT EXISTS (...)、②NOT IN (SELECT ... WHERE col IS NOT NULL)(NULL filtering is required),③LEFT JOIN ... WHERE right_key IS NULL, return the same result. NOT EXISTS is recommended for readability and safety.

ANTI-PATTERNS

Using a subquery with a nullable column in NOT IN：WHERE product_id NOT IN (SELECT product_id FROM order_items) returns zero rows if even one product_id is NULL. Always use NOT EXISTS, or add WHERE col IS NOT NULL inside NOT IN.

Code that assumes “this column cannot contain NULL”：Even if schema constraints or application validation prevent NULL, data migrations, bugs, or DB configuration changes can introduce it. Make NOT EXISTS your default choice for anti-joins.

Practical column: Three anti-join patterns and performance

In PostgreSQL, NOT EXISTS and LEFT JOIN IS NULL are usually transformed into nearly identical plans (Anti Merge Join / Anti Hash Join). Check with EXPLAIN ANALYZE; an “Anti Join” plan is optimal. Avoid NOT IN (subquery) unless NULL is ruled out. For large tables, NOT EXISTS plus an index on the foreign key is often the most efficient choice.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.

QUESTION 3

IN (Subquery) Predicate — Dynamic sets, semi-joins, and the NOT IN NULL trap

IN SubqueriesSemi-JoinDynamic ListNOT IN Warning

Background ▼

IN (subquery) checks whether a column value belongs to the set returned by a subquery. It is common in production because it dynamically derives a set from the table's current state instead of using a fixed list.

WHERE o.user_id IN (
  SELECT user_id        -- Dynamically generate the ID list of premium members with a subquery
  FROM   users
  WHERE  plan = 'premium'
)
-- ↑ Non-correlated subquery: evaluated once to build the set; often equivalent to EXISTS

NOT IN + NULL trap (recap): If a subquery returns NULL, NOT IN becomes UNKNOWN for every row → zero rows (see Q2). Always use NOT EXISTS, or add WHERE col IS NOT NULL before using NOT IN.

Problem ▼

From the orders table, retrieve orders placed by premium members (plan = 'premium'). Use a subquery against users, return order_id, user_id, amount, status, ordered_at, and order by ordered_at ascending.

Tables used

▸ orders

order_id	user_id	amount	status	ordered_at
101	1	15000	completed	2024-05-01
102	1	8000	cancelled	2024-05-10
103	2	5000	pending	2024-05-15
104	3	22000	completed	2024-05-20
105	4	3000	cancelled	2024-05-22
106	3	9000	completed	2024-05-25
107	5	12000	completed	2024-05-28

▸ users

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

Expected Output ▼

Expected output (ordered_at ascending):

order_id	user_id	amount	status	ordered_at
101	1	15000	completed	2024-05-01
102	1	8000	cancelled	2024-05-10
104	3	22000	completed	2024-05-20
106	3	9000	completed	2024-05-25

Note: orders 103, 105, and 107 from user_ids 2 (free), 4 (free), and 5 (free) are excluded.

Model Answer ▼

SELECT
  o.order_id, o.user_id, o.amount, o.status, o.ordered_at
FROM   orders o
WHERE  o.user_id IN (
  SELECT user_id          -- Dynamically retrieve premium members' user_id values
  FROM   users
  WHERE  plan = 'premium'  -- Subquery result: {1, 3}
)
ORDER BY o.ordered_at;

/*
  Execution order (SQL's logical evaluation order):
  1. Evaluate the non-correlated subquery → retrieve premium users once
  2. FROM orders o                      → read 7 rows
  3. WHERE o.user_id IN (...)           → filter to orders from matching users
  4. SELECT o.order_id, o.user_id, ...  → select 5 columns
  5. ORDER BY o.ordered_at              → sort by date ascending
  */

Explanation (table transitions & key points) ▼

SELECT
  o.order_id, o.user_id, o.amount, o.status, o.ordered_at
FROM   orders o
WHERE  o.user_id IN (
  SELECT user_id
  FROM   users
  WHERE  plan = 'premium'
)
ORDER BY o.ordered_at;

LEGEND ▼

Columns / keys under evaluation

Excluded / hidden data

✓ pass

✗ excluded

① Execute subquery (generate dynamic set) ▼

SELECT user_id FROM users WHERE plan = 'premium'The non-correlated subquery inside IN is executed once first, generating a dynamic set of premium member IDs.

1 / 4

user_id	name	plan	IN-set element
1	Taro Tanaka	premium	✓ Added (1)
2	Hanako Sato	free	✗ Excluded
3	Ichiro Suzuki	premium	✓ Added (3)
4	Jiro Yamada	free	✗ Excluded
5	Saburo Takahashi	free	✗ Excluded

Generated set: {1, 3}

LEARNING POINTS

IN (subquery) = semi-join:A non-correlated subquery in IN (SELECT ...) is evaluated once to build a set, then matched against each row of the outer query. In an execution plan it often appears as a Hash Semi Join or Merge Semi Join.

IN vs. EXISTS equivalence:For a non-correlated subquery, WHERE user_id IN (...) and WHERE EXISTS (...) are equivalent. Modern optimizers can transform one into the other, so they often produce similar plans; choose whichever is more readable.

Correlated vs. non-correlated:If the IN subquery does not reference an outer column, it is executed once (non-correlated). A correlated IN runs for each outer row, and may not be equivalent to EXISTS; in such cases, EXISTS expresses the intent more clearly.

ANTI-PATTERNS

NOT IN + NULL trap (recap):As in Q2, NOT IN (SELECT nullable_col ...) returns zero rows when NULL is included. The same trap applies to dynamic subqueries and fixed lists. If you use NOT IN, add WHERE col IS NOT NULL inside, or switch to NOT EXISTS.

Passing a large number of IDs to IN from an application:If a backend collects thousands or tens of thousands of IDs and embeds them as WHERE id IN (1, 2, ..., 50000), SQL parsing and planning costs rise sharply. Switch to a design for large sets, such as a temporary table, UNNEST, or JOIN.

Practical column: Building a dynamic IN clause in an application

In PostgreSQL, pass an array as one bind parameter with WHERE user_id = ANY($1::int[]) (safe for any length). ORMs such as Node.js / Prisma / Drizzle use this pattern internally. Hand-written fixed IN lists can drift from application state, so consider switching to an IN (subquery) pattern based on the table's current state.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.

QUESTION 4

ALL / ANY Predicates — Compare with all or some subquery results (∀ and ∃)

ALLANYSet Comparison∀ / ∃ Quantifiers

Background ▼

ANY (= SOME) returns TRUE when a comparison is true for at least one row in the subquery's set; ALL returns TRUE only when it is true for every row.

WHERE price > ANY (SELECT price FROM products WHERE category = 'Accessories')
-- ↑ Equivalent to price > MIN(accessory prices): “greater than any” means greater than the smallest value in the set

WHERE price > ALL (SELECT price FROM products WHERE category = 'Accessories')
-- ↑ Equivalent to price > MAX(accessory prices): “greater than all” means greater than the largest value in the set

= ANY is equivalent to IN: category = ANY (ARRAY['PC', 'Tablet']) is exactly equivalent to IN ('PC', 'Tablet'). ANY / ALL show their real value when used with comparison operators such as > and <.

Problem ▼

From the products table, retrieve products priced higher than every product in the Accessories category using the ALL predicate. Return product_id, name, category, price, ordered by price ascending.

Tables used

▸ products

product_id	name	category	price	stock
1	Smartphone X	Smartphone	89800	50
2	Smartbook Pro	PC	128000	0
3	Tablet Air	Tablet	64800	30
4	Wireless Earbuds	Accessories	12800	100
5	USB Cable	Accessories	980	200
6	Gaming PC Pro	PC	198000	5

Expected Output ▼

Expected output (price ascending):

product_id	name	category	price
3	Tablet Air	Tablet	64800
1	Smartphone X	Smartphone	89800
2	Smartbook Pro	PC	128000
6	Gaming PC Pro	PC	198000

Note: the maximum Accessories price is 12,800 yen. The 4 products above satisfy price > ALL {12800, 980} = price > 12800.
Product 4 (12800) fails because 12800 > 12800 is false; product 5 (980) fails both comparisons → excluded.

Model Answer ▼

SELECT
  product_id, name, category, price
FROM   products
WHERE  price > ALL (
  SELECT price                       -- Price list for all Accessories products: {12800, 980}
  FROM   products
  WHERE  category = 'Accessories'
)
ORDER BY price;                      -- ↑ Equivalent to price > MAX(Accessories prices) = price > 12800

/*
  Execution order (SQL's logical evaluation order):
  1. Evaluate the subquery                  → generate the Accessories price set
  2. FROM products                         → read 6 rows
  3. WHERE price > ALL (...)               → filter above the maximum
  4. SELECT product_id, name, ...  → select 4 columns
  5. ORDER BY price                        → sort by price ascending
  */

Explanation (table transitions & key points) ▼

SELECT
  product_id, name, category, price
FROM   products
WHERE  price > ALL (
  SELECT price
  FROM   products
  WHERE  category = 'Accessories'
)
ORDER BY price;

LEGEND ▼

Columns / keys under evaluation

① Execute subquery (generate comparison set) ▼

SELECT price FROM products WHERE category = 'Accessories'Execute the subquery and retrieve the price set for comparison: Accessories.

1 / 4

product_id	name	category	price (add to set)
4	Wireless Earbuds	Accessories	12800
5	USB Cable	Accessories	980

Subquery result: {12800, 980}

LEARNING POINTS

ANY is “∃ (existential quantifier)”:price > ANY (set) means price is greater than at least one member of the set → it is equivalent to price > MIN(set). Also, = ANY is exactly equivalent to IN.

ALL is “∀ (universal quantifier)”:price > ALL (set) means price is greater than every element of the set → it is equivalent to price > MAX(set). Also, <> ALL is equivalent to NOT IN when NULL is absent.

Truth values for an empty set:ANY (empty set) is always FALSE (there is nothing to compare); ALL (empty set) is always TRUE (vacuous truth). Be careful when an ALL subquery can be empty.

ANTI-PATTERNS

A design that can return an empty set to ALL:If Accessories has 0 rows, price > ALL (empty set) is always TRUE and every product is returned. This reverses the intended WHERE condition. If the subquery may be empty, check with EXISTS first or provide a default with COALESCE.

MAX / MIN may be easier to optimize than ANY / ALL:price > ANY (...) is equivalent to price > (SELECT MIN(price) ...), but some databases can optimize the scalar subquery and index usage more easily. Check with EXPLAIN and consider rewriting to MIN / MAX if it is slow.

Practical column: Choosing between = ANY and IN

In PostgreSQL, pass an array parameter directly with col = ANY($1::int[]) (the cleanest way to pass an IN list as an array). The optimizer treats = ANY (subquery) and IN (subquery) as equivalent, so the more readable IN is commonly used. For comparison operators such as > ANY and > ALL, explicitly rewriting to a MIN / MAX subquery can make the query's intent clearer.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.

QUESTION 5

IS DISTINCT FROM Predicate — NULL-safe equality and the trap of <>

IS DISTINCT FROMNULL-Safe ComparisonNULL HandlingThe <> Trap

Background ▼

IS DISTINCT FROM is a predicate for safely comparing equality when NULL may be present. Ordinary <> produces UNKNOWN when compared with NULL and the row is excluded, whereas IS DISTINCT FROM treats NULL as a “definitely different value” and returns TRUE.

-- Truth table for a IS DISTINCT FROM b
'WINTER20' IS DISTINCT FROM 'SUMMER10'  -- → TRUE  (different values)
NULL       IS DISTINCT FROM 'SUMMER10'  -- → TRUE  (NULL is treated as different)
'SUMMER10' IS DISTINCT FROM 'SUMMER10'  -- → FALSE (same value)
NULL       IS DISTINCT FROM NULL        -- → FALSE (both NULL are treated as equal)

The <> trap: With coupon_code <> 'SUMMER10', rows where coupon_code is NULL evaluate to UNKNOWN → excluded. Use IS DISTINCT FROM when you also want to include rows where the coupon was unused (NULL).

Problem ▼

From the orders table, retrieve orders whose coupon_code differs from 'SUMMER10', including rows where the coupon was unused (NULL). Use IS DISTINCT FROM, return order_id, user_id, amount, coupon_code, ordered_at, and order by order_id ascending.

Tables used

▸ orders

order_id	user_id	amount	coupon_code	ordered_at
101	1	15000	SUMMER10	2024-05-01
102	1	8000	NULL	2024-05-10
103	2	5000	NULL	2024-05-15
104	3	22000	WINTER20	2024-05-20
105	4	3000	NULL	2024-05-22
106	3	9000	NULL	2024-05-25
107	5	12000	SUMMER10	2024-05-28

Expected Output ▼

Expected output (order_id ascending):

order_id	user_id	amount	coupon_code	ordered_at
102	1	8000	NULL	2024-05-10
103	2	5000	NULL	2024-05-15
104	3	22000	WINTER20	2024-05-20
105	4	3000	NULL	2024-05-22
106	3	9000	NULL	2024-05-25

Note: orders 101 and 107 (SUMMER10) are excluded. NULL rows (102, 103, 105, and 106) are included as “different from SUMMER10.”

Model Answer ▼

SELECT
  order_id, user_id, amount, coupon_code, ordered_at
FROM   orders
WHERE  coupon_code IS DISTINCT FROM 'SUMMER10'
--              ↑ NULL-safe comparison: NULL is treated as different from SUMMER10 = TRUE
--   ✗ coupon_code <> 'SUMMER10' makes NULL rows UNKNOWN → they are excluded
ORDER BY order_id;

/*
  Execution order (SQL's logical evaluation order):
  1. FROM orders                                    → read 7 rows
  2. WHERE coupon_code IS DISTINCT FROM 'SUMMER10'  → keep non-matching rows
  3. SELECT order_id, user_id, amount, ...          → select 5 columns
  4. ORDER BY order_id                              → sort by order_id ascending

  ▸ IS DISTINCT FROM truth table (recap):
      a = b             → FALSE  (equal)
      a ≠ b             → TRUE   (different values)
      NULL vs non-NULL  → TRUE   (NULL is “different”)
      NULL vs NULL      → FALSE  (both NULL are “equal”)
*/

Explanation (table transitions & key points) ▼

SELECT
  order_id, user_id, amount, coupon_code, ordered_at
FROM   orders
WHERE  coupon_code IS DISTINCT FROM 'SUMMER10'
ORDER BY order_id;

LEGEND ▼

Rows read / loaded

① FROM orders ▼

FROM ordersRead all 7 rows from orders. The coupon_code column includes NULL (unused coupon).

1 / 3

order_id	user_id	coupon_code	ordered_at
101	1	SUMMER10	2024-05-01
102	1	NULL	2024-05-10
103	2	NULL	2024-05-15
104	3	WINTER20	2024-05-20
105	4	NULL	2024-05-22
106	3	NULL	2024-05-25
107	5	SUMMER10	2024-05-28

Read all 7 rows

LEARNING POINTS

IS DISTINCT FROM truth table:① same value → FALSE; ② different values → TRUE; ③ NULL vs. non-NULL → TRUE (NULL is “different”); ④ NULL vs. NULL → FALSE (both NULL are “equal”). Unlike <>, it always returns a two-valued result (TRUE/FALSE).

Fundamental difference from <>:NULL <> 'SUMMER10' returns UNKNOWN and is excluded by WHERE (see the three-valued logic basics, Q4). NULL IS DISTINCT FROM 'SUMMER10' returns TRUE and includes the row. Use IS DISTINCT FROM when “no value is set” should count as different from the comparison value.

Choosing between COALESCE and IS DISTINCT FROM:COALESCE(coupon_code, '') <> 'SUMMER10' can also let NULL rows pass, but it risks unintended results if the default equals the comparison value. IS DISTINCT FROM is more general and safer.

ANTI-PATTERNS

NULL rows are missed with col <> 'value':Using <> on a nullable column makes NULL rows UNKNOWN, so they fall out of the filter. A query intended to retrieve “rows that are neither X nor NULL” may silently exclude NULL rows—one of the hardest production bugs to detect.

A COALESCE default collides with the comparison value:With COALESCE(coupon_code, 'SUMMER10') <> 'SUMMER10', NULL rows are converted to 'SUMMER10' and excluded (the opposite of the intent). If you use COALESCE, choose a default that cannot collide with the comparison value.

Practical column: DBMS support and alternative syntax for IS DISTINCT FROM

IS DISTINCT FROM is standardized in SQL:1999 and is directly available in PostgreSQL, DuckDB, CockroachDB, and others. MySQL 8.0 and later provide <=> (NULL-safe equality), where NOT (a <=> b) is equivalent to IS DISTINCT FROM. SQL Server supports IS DISTINCT FROM from 2022 onward; before that, a verbose form such as CASE WHEN a = b OR (a IS NULL AND b IS NULL) THEN 0 ELSE 1 END = 1 was required. Prepare the appropriate alternative syntax for the target DBMS.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.