SQL 19 🛢️ Set Filtering with IN and NOT IN

A comparison operator tests one value. The BETWEEN operator tests a range. The IN operator tests a set. It answers the question “Is this value one of these values?” The NOT IN operator answers the opposite question: “Is this value not one of these values?” The two operators are the tool for filtering by membership in a list.

The previous chapters covered the comparison operators, the logical operators, and the range filter. This chapter covers the set filter. It is part of the WHERE clause, and it is used wherever a column must be tested against a list of possible values. The list can be a literal list of values, or it can be a subquery that returns a set of values.

Key point: The IN operator is equivalent to a chain of OR comparisons. value IN (a, b, c) is equivalent to value = a OR value = b OR value = c. The NOT IN operator is equivalent to a chain of AND comparisons. value NOT IN (a, b, c) is equivalent to value <> a AND value <> b AND value <> c. The equivalence is exact — the optimizer treats both forms the same way. The IN operator is cleaner and less error-prone.

---

Why the IN operator matters

A list filter is one of the most common filtering patterns. “Which customers are in New York, Chicago, or Boston?” “Which orders are in the pending, shipped, or delivered status?” “Which products have a category of electronics, appliances, or furniture?” The IN operator expresses all of these.

The syntax problem. A chain of OR comparisons works: city = 'New York' OR city = 'Chicago' OR city = 'Boston'. But the pattern is verbose. The column city is repeated for each value. The IN operator removes the repetition: city IN ('New York', 'Chicago', 'Boston'). The intent is clearer, and the query is shorter.

The readability problem. A list of ten values produces a ten-line OR chain. The IN operator produces a single line. The query is easier to read and easier to maintain. When a value is added or removed, only the list changes.

The subquery problem. A list can come from a subquery. The IN operator accepts a subquery that returns a single column. The IN operator with a subquery is the standard way to filter by membership in a set that is computed at runtime. For example, “Which customers have placed an order in the last month?” The set of customer IDs comes from the orders table, and the IN operator filters the customers table by that set.

The NULL problem. A NULL value is not equal to any value in the list. The expression NULL IN (1, 2, 3) returns NULL, not TRUE or FALSE. The row with the null value is excluded from the result. The NOT IN operator has a worse trap: if the list or the subquery contains a NULL, the result is NULL for every row, and no rows are returned.

The trade-off. The IN operator is a syntactic convenience for a literal list. It does not add new functionality. The optimizer treats IN and the equivalent OR chain the same way. The benefit is readability and the ability to use a subquery.

---

a. The IN Operator

The IN operator tests whether a value is one of a set of values. The syntax is:

value IN (value1, value2, value3, ...)

The expression is equivalent to:

value = value1 OR value = value2 OR value = value3 OR ...

The query selects customers in New York, Chicago, or Boston:

SELECT * FROM customers WHERE city IN ('New York', 'Chicago', 'Boston');

The query includes customers whose city is any of the three values. The order of the values in the list does not matter.

The IN operator can be used with any type:

SELECT * FROM orders WHERE status IN ('pending', 'shipped', 'delivered');
SELECT * FROM products WHERE category IN ('Electronics', 'Appliances');
SELECT * FROM employees WHERE department_id IN (10, 20, 30);
SELECT * FROM events WHERE event_date IN ('2026-01-01', '2026-06-15', '2026-12-25');

The first query selects orders in one of three statuses. The second selects products in one of two categories. The third selects employees in one of three departments. The fourth selects events on one of three specific dates.

The IN operator can also be used with a subquery:

SELECT * FROM customers WHERE customer_id IN (SELECT customer_id FROM orders WHERE total > 200);

The subquery returns a set of customer IDs. The outer query selects the customers whose ID is in that set. The subquery must return a single column.

The IN operator with a subquery is the standard way to filter by membership in a dynamically computed set. The subquery is evaluated once, and the result is used as the list.

---

b. The NOT IN Operator

The NOT IN operator is the negation of IN. It tests whether a value is not one of a set of values.

SELECT * FROM customers WHERE city NOT IN ('New York', 'Chicago');

The query selects customers whose city is not New York or Chicago. The expression is equivalent to:

value <> value1 AND value <> value2 AND ...

The NOT IN operator uses AND, not OR, because the condition is the negation of a set membership. A value is not in the set if it is different from every value in the set.

The NOT IN operator can be used with a subquery:

SELECT * FROM customers WHERE customer_id NOT IN (SELECT customer_id FROM orders);

The query selects customers who have never placed an order. The subquery returns the set of customer IDs that have placed orders. The outer query selects the customers whose ID is not in that set.

The NOT IN operator is the right tool for the “not in the set” question. But it has a dangerous trap when the subquery returns a NULL.

---

c. The NULL Trap in NOT IN

The NOT IN operator has a well-known trap. If the list or the subquery contains a NULL, the result is NULL for every row, and no rows are returned.

The reason is the three-valued logic. The expression value NOT IN (a, NULL) is equivalent to:

value <> a AND value <> NULL

The value <> NULL comparison returns NULL, not TRUE. The AND operator returns NULL when one operand is NULL and the other is TRUE or NULL. The result is NULL for every row where value <> a is TRUE. The WHERE clause includes only the rows where the condition is TRUE. No rows are returned.

The trap is silent. The query runs without an error. It just returns no rows. The developer assumes there are no matching rows. The actual reason is the NULL in the subquery.

-- This returns no rows if the subquery returns any NULL
SELECT * FROM customers WHERE customer_id NOT IN (SELECT customer_id FROM orders);

-- The subquery may return NULL if any order has a null customer_id

The safe alternative is the NOT EXISTS operator:

SELECT * FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

The NOT EXISTS operator uses a correlated subquery. It checks whether a matching row exists for each row in the outer query. It does not compare sets, so it does not have the NULL trap. It is the recommended alternative for the “not in the set” question when the subquery may return NULL.

The difference is not just about performance. The NOT IN and NOT EXISTS operators produce the same result when the subquery contains no NULL. They produce different results when the subquery contains a NULL. The NOT EXISTS operator handles the NULL correctly. The NOT IN operator does not.

---

Complete Example Session

This session demonstrates the IN and NOT IN operators on a small database.

-- ============================================
-- PART 1: THE TABLES
-- ============================================

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    first_name  VARCHAR(50) NOT NULL,
    last_name   VARCHAR(50) NOT NULL,
    city        VARCHAR(50)
);

CREATE TABLE orders (
    order_id    INT PRIMARY KEY,
    customer_id INT,
    order_date  DATE NOT NULL,
    total       DECIMAL(10, 2) NOT NULL
);

INSERT INTO customers VALUES
    (1, 'Alice', 'Johnson', 'New York'),
    (2, 'Bob', 'Smith', 'Chicago'),
    (3, 'Carol', 'Williams', 'Boston'),
    (4, 'Dave', 'Brown', NULL),
    (5, 'Eve', 'Davis', 'New York');

INSERT INTO orders VALUES
    (1001, 1, '2026-01-15', 99.99),
    (1002, 1, '2026-02-20', 149.99),
    (1003, 2, '2026-03-10', 49.99),
    (1004, 3, '2026-04-05', 299.99),
    (1005, NULL, '2026-05-12', 199.99);

-- ============================================
-- PART 2: IN WITH A LITERAL LIST
-- ============================================

SELECT * FROM customers WHERE city IN ('New York', 'Chicago');

-- Output: Alice, Bob, Eve

-- ============================================
-- PART 3: IN WITH NUMBERS
-- ============================================

SELECT * FROM orders WHERE total IN (99.99, 149.99, 299.99);

-- Output: 1001, 1002, 1004

-- ============================================
-- PART 4: IN WITH DATES
-- ============================================

SELECT * FROM orders WHERE order_date IN ('2026-01-15', '2026-03-10');

-- Output: 1001, 1003

-- ============================================
-- PART 5: IN WITH A SUBQUERY
-- ============================================

SELECT * FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders WHERE total > 100);

-- Output: Alice, Bob, Carol
-- Dave and Eve are not included.

-- ============================================
-- PART 6: NOT IN
-- ============================================

SELECT * FROM customers WHERE city NOT IN ('New York', 'Chicago');

-- Output: Carol
-- Dave is excluded because city is NULL.

-- ============================================
-- PART 7: THE NOT IN TRAP
-- ============================================

SELECT * FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);

-- Output: empty
-- The subquery returns NULL (order 1005 has a null customer_id).
-- The NOT IN comparison returns NULL for every row.
-- No rows are returned.

-- ============================================
-- PART 8: THE NOT EXISTS ALTERNATIVE
-- ============================================

SELECT * FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

-- Output: Dave, Eve
-- Dave and Eve have not placed an order.
-- The NOT EXISTS operator handles the NULL correctly.

-- ============================================
-- PART 9: THE EQUIVALENT EXPRESSIONS
-- ============================================

-- value IN (a, b, c)
-- is equivalent to
-- value = a OR value = b OR value = c

-- value NOT IN (a, b, c)
-- is equivalent to
-- value <> a AND value <> b AND value <> c

-- ============================================
-- PART 10: THE SUMMARY
-- ============================================

-- IN : value is one of the set
-- NOT IN : value is not one of the set
-- Subquery : the set comes from a query
-- NULL trap : NOT IN returns no rows if the set contains NULL
-- Alternative : NOT EXISTS handles the NULL correctly

The ten parts cover the tables, IN with a literal list, IN with numbers, IN with dates, IN with a subquery, NOT IN, the NOT IN trap, the NOT EXISTS alternative, the equivalent expressions, and the summary.

---

Quick Reference

The IN Operators

Operator	Purpose
IN (list)	Value is one of the list
NOT IN (list)	Value is not one of the list
IN (subquery)	Value is in the subquery’s result
NOT IN (subquery)	Value is not in the subquery’s result

The Equivalent Expressions

IN	Equivalent
x IN (a, b, c)	x = a OR x = b OR x = c
x NOT IN (a, b, c)	x <> a AND x <> b AND x <> c

The NULL Behavior

Expression	Result
NULL IN (1, 2, 3)	NULL
1 IN (1, 2, NULL)	TRUE
4 IN (1, 2, NULL)	NULL
1 NOT IN (1, 2, NULL)	FALSE
4 NOT IN (1, 2, NULL)	NULL

The NOT IN Trap

Scenario	Result
Subquery has no NULL	Correct rows returned
Subquery has a NULL	No rows returned
NOT EXISTS instead	Correct rows returned

---

Best Practices

✅ Do This:

-- Use IN with a literal list
WHERE city IN ('New York', 'Chicago', 'Boston')                  -- ✅

-- Use IN with a subquery
WHERE customer_id IN (SELECT customer_id FROM orders WHERE total > 200) -- ✅

-- Use NOT EXISTS instead of NOT IN with a subquery
WHERE NOT EXISTS (SELECT 1 FROM orders WHERE ...)               -- ✅

-- Filter the subquery to exclude NULL
WHERE id NOT IN (SELECT id FROM t WHERE id IS NOT NULL)         -- ✅

❌ Don’t Do This:

-- Don't use NOT IN with a subquery that may return null
WHERE customer_id NOT IN (SELECT customer_id FROM orders)       -- ❌

-- Don't expect IN to match null
WHERE city IN ('New York', NULL)  -- only matches New York      -- ⚠️

-- Don't use a subquery that returns multiple columns
WHERE id IN (SELECT id, name FROM t)                             -- ❌

-- Don't forget that NOT IN excludes null rows in the outer table
WHERE city NOT IN ('New York')  -- excludes null cities         -- ⚠️

---

Common Pitfalls

Pitfall	Why It Happens	Fix
NOT IN returns no rows	Subquery returns NULL	Use NOT EXISTS
IN doesn’t match null	NULL IN (...) returns NULL	Use IS NULL
Subquery returns multiple columns	Wrong subquery	Select one column
Empty result	Case mismatch	Check the collation
Slow query	Large subquery	Use EXISTS or a join

---

Real-World Examples

1. IN with a Literal List

SELECT * FROM customers WHERE city IN ('New York', 'Chicago', 'Boston');

2. IN with Numbers

SELECT * FROM orders WHERE status_id IN (1, 3, 5);

3. IN with Dates

SELECT * FROM events WHERE event_date IN ('2026-01-01', '2026-06-15');

4. IN with a Subquery

SELECT * FROM customers WHERE customer_id IN (SELECT customer_id FROM orders WHERE total > 200);

5. NOT IN

SELECT * FROM customers WHERE city NOT IN ('New York', 'Chicago');

6. NOT IN with Subquery (Trap)

SELECT * FROM customers WHERE customer_id NOT IN (SELECT customer_id FROM orders);

7. NOT EXISTS (Safe)

SELECT * FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

8. IN with NOT

SELECT * FROM customers WHERE NOT (city IN ('New York', 'Chicago'));

9. Filter the Subquery

SELECT * FROM customers WHERE customer_id NOT IN (SELECT customer_id FROM orders WHERE customer_id IS NOT NULL);

10. Combine with Other Operators

SELECT * FROM orders WHERE status IN ('pending', 'shipped') AND total > 100;

---

Visual

The IN Operator

┌──────────────────────────────────────────────┐
│  value IN (a, b, c)                          │
│                                              │
│  Equivalent to:                              │
│    value = a OR value = b OR value = c       │
│                                              │
│  TRUE if the value matches any element.      │
│  FALSE if the value matches none.            │
│  NULL if the value is NULL.                  │
│                                              │
└──────────────────────────────────────────────┘

The NOT IN Operator

┌──────────────────────────────────────────────┐
│  value NOT IN (a, b, c)                      │
│                                              │
│  Equivalent to:                              │
│    value <> a AND value <> b AND value <> c  │
│                                              │
│  TRUE if the value matches none.             │
│  FALSE if the value matches any.             │
│  NULL if the value is NULL.                  │
│                                              │
└──────────────────────────────────────────────┘

The NOT IN Trap

┌──────────────────────────────────────────────┐
│  NOT IN TRAP                                 │
│                                              │
│  WHERE id NOT IN (SELECT id FROM t)          │
│    └─ Subquery returns {1, 2, NULL}          │
│                                              │
│  For id = 3:                                 │
│    3 <> 1 (TRUE)                             │
│    3 <> 2 (TRUE)                             │
│    3 <> NULL (NULL)                          │
│    TRUE AND TRUE AND NULL = NULL             │
│                                              │
│  WHERE includes only TRUE.                   │
│  No rows returned.                           │
│                                              │
└──────────────────────────────────────────────┘

The Safe Alternative

┌──────────────────────────────────────────────┐
│  NOT EXISTS                                  │
│                                              │
│  SELECT * FROM customers c                   │
│  WHERE NOT EXISTS (                          │
│    SELECT 1 FROM orders o                    │
│    WHERE o.customer_id = c.customer_id       │
│  );                                          │
│                                              │
│  For each customer, check if a matching      │
│  order exists. If not, include the customer. │
│                                              │
│  The NULL in the orders table does not       │
│  affect the result.                          │
│                                              │
└──────────────────────────────────────────────┘

---

Summary

Item	Value
IN (list)	Value is one of the list
NOT IN (list)	Value is not one of the list
IN equivalent	Chain of OR
NOT IN equivalent	Chain of AND
IN with subquery	Filter by a computed set
NOT IN trap	Returns no rows if the set contains NULL
NOT EXISTS	The safe alternative
NULL IN (...)	Returns NULL
NULL NOT IN (...)	Returns NULL

Key takeaways:

• The IN operator tests whether a value is one of a set of values. The expression value IN (a, b, c) is equivalent to value = a OR value = b OR value = c. It returns TRUE if the value matches any element, FALSE if it matches none, and NULL if the value is NULL .
• The NOT IN operator tests whether a value is not one of a set of values. The expression value NOT IN (a, b, c) is equivalent to value <> a AND value <> b AND value <> c. It returns TRUE if the value matches none, FALSE if it matches any, and NULL if the value is NULL .
• The IN operator can take a subquery. The subquery returns a single column. The IN operator filters the outer query by the set of values returned by the subquery. The subquery is the standard way to filter by a dynamically computed set .
• The NOT IN operator has a trap with NULL. If the list or the subquery contains a NULL, the result is NULL for every row, and no rows are returned. The query runs without an error. The result is silently wrong .
• The NOT EXISTS operator is the safe alternative. It uses a correlated subquery to check for the absence of a matching row. It does not compare sets, so it does not have the NULL trap. It is the recommended alternative for the “not in the set” question when the subquery may return NULL .
• A NULL value is not in any set. The expression NULL IN (1, 2, 3) returns NULL, not TRUE or FALSE. The row with the null value is excluded from the result. To include null rows, add OR column IS NULL .
• The IN operator works with any type. Numbers, strings, dates, and timestamps are all supported. The list of values can be a literal list or a subquery. The subquery must return a single column.

Remember: The IN operator tests set membership. It is equivalent to a chain of OR. The NOT IN operator tests the negation. It is equivalent to a chain of AND. The IN operator handles subqueries. The NOT IN operator has a NULL trap. The safe alternative is NOT EXISTS. The IN operator is the tool for the set, and the set is the list.

---

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!