How do I filter values using IN, BETWEEN, and NOT in SQL?

When you filter rows in SQL, you often need to match a value against a list of options, check whether it falls within a range, or exclude rows that satisfy a condition. Chaining many AND/OR comparisons for these cases quickly becomes hard to read. SQL provides three operators — IN, BETWEEN, and NOT — that make these filters expressive and concise.

In this tutorial you will learn how to use IN to match a value against a list, BETWEEN to match a value within an inclusive range, and NOT to negate any condition. You will also see how each one interacts with NULL and where readers commonly get tripped up.

Prerequisites

To follow along you need:

A working SQL database (PostgreSQL, MySQL, MariaDB, SQL Server, Oracle Database, or SQLite).
A client that can execute SQL statements.
Basic familiarity with SELECT, WHERE, and comparison/logical operators.

If you followed the previous tutorials in this series, you can reuse the same sample tables.

Sample Database

We continue with the online bookstore schema used earlier in the series.

CREATE TABLE authors (
    author_id   INTEGER      PRIMARY KEY,
    author_name VARCHAR(100) NOT NULL,
    country     VARCHAR(50)
);

CREATE TABLE books (
    book_id      INTEGER       PRIMARY KEY,
    title        VARCHAR(200)  NOT NULL,
    author_id    INTEGER       NOT NULL,
    category     VARCHAR(50),
    price        DECIMAL(8, 2) NOT NULL,
    stock        INTEGER       NOT NULL,
    published_on DATE,
    CONSTRAINT fk_books_author
        FOREIGN KEY (author_id) REFERENCES authors (author_id)
);

INSERT INTO authors (author_id, author_name, country) VALUES
    (1, 'Jane Austen',              'United Kingdom'),
    (2, 'Haruki Murakami',          'Japan'),
    (3, 'Chimamanda Ngozi Adichie', 'Nigeria'),
    (4, 'Gabriel Garcia Marquez',   'Colombia');

INSERT INTO books (book_id, title, author_id, category, price, stock, published_on) VALUES
    (1, 'Pride and Prejudice',           1, 'Classic',      12.50, 20, '1813-01-28'),
    (2, 'Emma',                          1, 'Classic',      10.00,  0, '1815-12-23'),
    (3, 'Norwegian Wood',                2, 'Fiction',      15.75, 12, '1987-09-04'),
    (4, 'Kafka on the Shore',            2, 'Fiction',      18.20,  5, '2002-09-12'),
    (5, 'Half of a Yellow Sun',          3, 'Historical',   16.00,  8, '2006-08-11'),
    (6, 'Americanah',                    3, 'Contemporary', 14.50,  3, '2013-05-14'),
    (7, 'One Hundred Years of Solitude', 4, 'Classic',      22.00, 25, '1967-05-30'),
    (8, 'Love in the Time of Cholera',   4, 'Classic',      19.99,  0, '1985-09-05'),
    (9, 'Unknown Title',                 2, NULL,           13.00,  4, NULL);

Row 9 intentionally uses NULL for category and published_on. We will use it to explore how IN, BETWEEN, and NOT handle missing values.

Basic Syntax

All three operators are used inside a boolean expression, most often in a WHERE clause.

SELECT column_list
FROM table_name
WHERE column_name IN (value1, value2, ...);

SELECT column_list
FROM table_name
WHERE column_name BETWEEN low_value AND high_value;

SELECT column_list
FROM table_name
WHERE NOT condition;

IN returns true when the column value equals any value in the list.
BETWEEN low AND high returns true when the column value is greater than or equal to low and less than or equal to high — both endpoints are included.
NOT inverts the boolean result of the expression that follows it.

Each of them can be combined with NOT: NOT IN, NOT BETWEEN, and IS NOT NULL.

Filtering with `IN`

IN is a readable shortcut for a chain of equality checks joined by OR.

Practical Example

“Show every book whose category is Classic, Fiction, or Historical.”

SELECT
    title,
    category
FROM books
WHERE category IN ('Classic', 'Fiction', 'Historical')
ORDER BY category, title;

Reading the query in logical order:

FROM books — start with every row in the books table.
WHERE category IN (...) — keep only rows whose category matches one of the listed values.
SELECT title, category — return these two columns.
ORDER BY category, title — sort the final result.

Expected Result

title	category
Emma	Classic
Love in the Time of Cholera	Classic
One Hundred Years of Solitude	Classic
Pride and Prejudice	Classic
Kafka on the Shore	Fiction
Norwegian Wood	Fiction
Half of a Yellow Sun	Historical

The row with category IS NULL is excluded, because NULL does not equal any listed value.

Equivalent Form with `OR`

The query above is equivalent to:

SELECT
    title,
    category
FROM books
WHERE category = 'Classic'
   OR category = 'Fiction'
   OR category = 'Historical';

IN communicates intent more clearly and is easier to maintain when the list changes.

Filtering with `BETWEEN`

BETWEEN matches values inside an inclusive range. It works with numbers, dates, and strings that share a comparable type.

Practical Example: Numeric Range

“Show every book priced between 12.00 and 16.00, inclusive.”

SELECT
    title,
    price
FROM books
WHERE price BETWEEN 12.00 AND 16.00
ORDER BY price;

Expected Result

title	price
Pride and Prejudice	12.50
Unknown Title	13.00
Americanah	14.50
Norwegian Wood	15.75
Half of a Yellow Sun	16.00

Both 12.00 and 16.00 would be included if they matched a row exactly. BETWEEN low AND high is equivalent to column >= low AND column <= high.

Practical Example: Date Range

BETWEEN also works with dates. Use the ANSI-standard DATE '...' literal for portability.

SELECT
    title,
    published_on
FROM books
WHERE published_on BETWEEN DATE '1900-01-01' AND DATE '1999-12-31'
ORDER BY published_on;

This returns twentieth-century books. The row with published_on IS NULL is excluded because comparisons against NULL yield unknown.

Watch the Order of Endpoints

BETWEEN requires the lower value first. If you reverse them, most databases silently return zero rows because no value can be both >= 16.00 and <= 12.00.

-- Returns no rows on standard-compliant databases.
SELECT title, price
FROM books
WHERE price BETWEEN 16.00 AND 12.00;

Negating with `NOT`

NOT inverts a boolean expression. It is most commonly used together with IN, BETWEEN, and LIKE, but it can negate any condition.

`NOT IN`

“Show every book whose category is neither Classic nor Fiction.”

SELECT
    title,
    category
FROM books
WHERE category NOT IN ('Classic', 'Fiction')
ORDER BY title;

Expected Result

title	category
Americanah	Contemporary
Half of a Yellow Sun	Historical

Notice that Unknown Title (with category IS NULL) is not in the result. This is a classic pitfall — see the Common Mistakes section.

`NOT BETWEEN`

“Show every book priced outside the 12.00–16.00 range.”

SELECT
    title,
    price
FROM books
WHERE price NOT BETWEEN 12.00 AND 16.00
ORDER BY price;

Expected Result

title	price
Emma	10.00
Kafka on the Shore	18.20
Love in the Time of Cholera	19.99
One Hundred Years of Solitude	22.00

NOT BETWEEN low AND high is equivalent to column < low OR column > high.

`NOT` with Other Conditions

You can apply NOT to any boolean expression, including a parenthesized combination:

SELECT
    title,
    category,
    stock
FROM books
WHERE NOT (category = 'Classic' AND stock = 0)
ORDER BY title;

“Show every book that is not an out-of-stock classic.”

Combining `IN`, `BETWEEN`, and `NOT`

These operators combine naturally with AND and OR to describe complex requirements.

“Show Classic or Fiction books, in stock, priced between 12.00 and 20.00.”

SELECT
    title,
    category,
    price,
    stock
FROM books
WHERE category IN ('Classic', 'Fiction')
  AND price BETWEEN 12.00 AND 20.00
  AND stock > 0
ORDER BY price;

Expected Result

title	category	price	stock
Pride and Prejudice	Classic	12.50	20
Norwegian Wood	Fiction	15.75	12
Kafka on the Shore	Fiction	18.20	5
Love in the Time of Cholera	Classic	19.99	0

Wait — Love in the Time of Cholera has stock = 0 and should be excluded. Reading the actual result, only the first three rows appear:

title	category	price	stock
Pride and Prejudice	Classic	12.50	20
Norwegian Wood	Fiction	15.75	12
Kafka on the Shore	Fiction	18.20	5

Each clause plays a clear role:

category IN ('Classic', 'Fiction') — restrict to two categories.
price BETWEEN 12.00 AND 20.00 — apply the price range.
stock > 0 — keep only books currently available.

Using These Operators in Application Code

When the values come from user input or a variable, always use parameterized queries.

String sql = """
        SELECT book_id, title, category, price
        FROM books
        WHERE category IN (?, ?, ?)
          AND price BETWEEN ? AND ?
        """;

try (PreparedStatement statement = connection.prepareStatement(sql)) {
    statement.setString(1, "Classic");
    statement.setString(2, "Fiction");
    statement.setString(3, "Historical");
    statement.setBigDecimal(4, new BigDecimal("12.00"));
    statement.setBigDecimal(5, new BigDecimal("20.00"));

    try (ResultSet resultSet = statement.executeQuery()) {
        while (resultSet.next()) {
            long bookId = resultSet.getLong("book_id");
            String title = resultSet.getString("title");
            String category = resultSet.getString("category");
            BigDecimal price = resultSet.getBigDecimal("price");
            // process the row...
        }
    }
}

Note that most JDBC drivers do not accept a Java collection directly for IN (...). You typically expand placeholders to match the list size or use database-specific features such as PostgreSQL’s = ANY(?) with an array parameter.

Common Mistakes

`NOT IN` with a List That Contains `NULL`

This is the most surprising pitfall in this tutorial. If any value in the NOT IN list is NULL, the whole condition evaluates to unknown for every row, so no rows are returned.

Incorrect:

SELECT title, category
FROM books
WHERE category NOT IN ('Classic', 'Fiction', NULL);

This query returns zero rows on every standard-compliant database. It is equivalent to:

WHERE category <> 'Classic'
  AND category <> 'Fiction'
  AND category <> NULL   -- always unknown

Because category <> NULL is always unknown, the entire AND chain never evaluates to true.

Correct — filter out NULL in the source or the list before applying NOT IN:

SELECT title, category
FROM books
WHERE category IS NOT NULL
  AND category NOT IN ('Classic', 'Fiction');

Forgetting That `NOT IN` Excludes `NULL` Rows Too

Even without NULL in the list, NOT IN excludes rows where the column itself is NULL, because NULL <> 'Classic' is unknown.

If you want those rows included, add an explicit check:

SELECT title, category
FROM books
WHERE category NOT IN ('Classic', 'Fiction')
   OR category IS NULL;

Reversing the `BETWEEN` Endpoints

BETWEEN 16.00 AND 12.00 is not the same as BETWEEN 12.00 AND 16.00. The lower value must come first, otherwise the range is empty.

Assuming `BETWEEN` Is Exclusive

BETWEEN is inclusive on both ends. If you need an exclusive upper bound (common with dates), write the comparison explicitly:

SELECT title, published_on
FROM books
WHERE published_on >= DATE '2000-01-01'
  AND published_on <  DATE '2010-01-01';

This is safer than BETWEEN DATE '2000-01-01' AND DATE '2009-12-31', which can behave differently when the column stores a timestamp with a time component.

Comparing to `NULL` with `=` or `<>`

NOT does not rescue NULL comparisons. Both column = NULL and NOT (column = NULL) evaluate to unknown. Always use IS NULL / IS NOT NULL for missing-value checks.

Database Compatibility

IN, BETWEEN, NOT, NOT IN, and NOT BETWEEN are part of ANSI SQL and work in:

PostgreSQL
MySQL
MariaDB
SQL Server
Oracle Database
SQLite

Notable considerations:

List length limits for IN. Oracle Database limits the IN list to 1000 expressions. Other systems allow more but may perform poorly with very long lists. For large sets, consider a subquery or a temporary table instead.
IN with a subquery. All major systems support WHERE column IN (SELECT ...). Be aware of NULL in the subquery result — the same NOT IN pitfall applies.
Date literals in BETWEEN. The DATE '2000-01-01' form is portable to PostgreSQL, MySQL, MariaDB, SQLite, and Oracle Database. SQL Server accepts the plain string '2000-01-01'.
Arrays and ANY/ALL. PostgreSQL supports = ANY(array) as a convenient alternative to IN when passing a single array parameter from application code.

When in doubt, consult the documentation for your database and version.

Best Practices

Prefer IN over long OR chains. It reads better and is easier to maintain.
Prefer BETWEEN for inclusive numeric ranges. For date/timestamp ranges, prefer explicit >= and < comparisons to avoid boundary bugs.
Handle NULL explicitly with NOT IN. Filter with IS NOT NULL first, or ensure your list never contains NULL.
Keep IN lists short. Very long lists reduce readability and may hit database-specific limits. Consider a subquery, a join, or a temporary table for large sets.
Parenthesize when combining with AND and OR. Even though IN, BETWEEN, and NOT bind clearly on their own, mixing them with OR can still cause precedence bugs.
Use parameters, not string concatenation. This prevents SQL injection and lets the database reuse the execution plan.
Verify destructive filters with SELECT first. Especially when using NOT IN or NOT BETWEEN, run a SELECT to confirm the target rows before any UPDATE or DELETE.

Warning: Running UPDATE or DELETE with NOT IN on a column that contains NULL values can produce unexpected results. Practice these statements only in a disposable learning database or after taking a verified backup.

Conclusion

You learned how to use IN to match a value against a list, BETWEEN to match an inclusive range, and NOT to negate any of these conditions. You also saw how each operator interacts with NULL — most importantly, why NOT IN combined with a NULL in the list returns no rows at all.

Prerequisites

Sample Database

Basic Syntax

Filtering with IN

Practical Example

Expected Result

Equivalent Form with OR

Filtering with BETWEEN

Practical Example: Numeric Range

Expected Result

Practical Example: Date Range

Watch the Order of Endpoints

Negating with NOT

NOT IN

Expected Result

NOT BETWEEN

Expected Result

NOT with Other Conditions

Combining IN, BETWEEN, and NOT

Expected Result

Using These Operators in Application Code

Common Mistakes

NOT IN with a List That Contains NULL

Forgetting That NOT IN Excludes NULL Rows Too

Reversing the BETWEEN Endpoints

Assuming BETWEEN Is Exclusive

Comparing to NULL with = or <>

Database Compatibility

Best Practices

Conclusion

Leave a ReplyCancel reply

Filtering with `IN`

Equivalent Form with `OR`

Filtering with `BETWEEN`

Negating with `NOT`

`NOT IN`

`NOT BETWEEN`

`NOT` with Other Conditions

Combining `IN`, `BETWEEN`, and `NOT`

`NOT IN` with a List That Contains `NULL`

Forgetting That `NOT IN` Excludes `NULL` Rows Too

Reversing the `BETWEEN` Endpoints

Assuming `BETWEEN` Is Exclusive

Comparing to `NULL` with `=` or `<>`