SQL NOT IN Operator: Syntax, Usage, and Examples

The NOT IN operator filters out rows where a value matches anything in a list or subquery. You use it when you want the opposite of IN, meaning “show me everything except these values.”

How to Use the NOT IN operator

You can use the NOT IN operator inside a WHERE clause. It checks if a column value is not included in a given list.

Basic syntax (with a list of values)

SELECT column_name
FROM table_name
WHERE column_nameNOTIN (value1, value2, value3);

Syntax (with a subquery)

SELECT column_name
FROM table_name
WHERE column_nameNOTIN (
SELECT column_name
FROM other_table
WHEREcondition
);

In both cases, the parentheses matter because NOT IN expects a list-like result.

When to Use the NOT IN operator

The NOT IN operator is perfect when you want to exclude a known set of results. Here are a few situations where it really shines.

1) Excluding a list of values

Sometimes you already know which values you don’t want. Maybe you’re filtering out canceled orders, internal test accounts, or certain categories.

Example idea:

• show orders except "Canceled" and "Refunded"
• show users except "guest" and "anonymous"

2) Filtering out results from another table

You can use NOT IN with a subquery when you want to exclude values found somewhere else.

Common example:

• show customers who haven’t placed an order
• show products that aren’t in the “featured” list

3) Cleaning up reports by removing outliers

For analytics and dashboards, you might remove a few known values that distort the data. For example, internal traffic sources, admin accounts, or demo records.

4) Narrowing down missing relationships

When you work with relational databases, you often want to find “things that don’t have a match.” NOT IN helps answer questions like:

• Which employees are not assigned to a team?
• Which students are not enrolled in any course?

Examples of the NOT IN operator

Let’s walk through a few common examples that you can copy, run, and adapt.

Example 1: Exclude specific categories

You’re building a report and want to ignore categories that aren’t relevant.

SELECT product_id, product_name, category
FROM products
WHERE categoryNOTIN ('Gift Card','Promo Item');

This returns all products except those in the two excluded categories.

Example 2: Exclude multiple statuses

Imagine an orders table where you only want active orders.

SELECT order_id, customer_id, status
FROM orders
WHERE statusNOTIN ('Canceled','Refunded','Fraud');

This is a clean way to say “only keep orders that are still valid.”

Example 3: Find customers who never placed an order

You have a customers table and an orders table.

SELECT customer_id, full_name
FROM customers
WHERE customer_idNOTIN (
SELECT customer_id
FROM orders
);

This finds customers whose customer_id is missing from the orders table.

It’s a classic “who hasn’t done X?” query.

Example 4: Filter using a date-based subquery

Let’s say you want all products that were not purchased during a sale period.

SELECT product_id, product_name
FROM products
WHERE product_idNOTIN (
SELECT product_id
FROM order_items
WHERE order_dateBETWEEN'2026-01-01'AND'2026-01-07'
);

This can help compare “sale week vs normal week” behavior.

Learn More About the NOT IN operator

The NOT IN operator is simple, but there are a few gotchas that can bite you if you don’t know about them.

NOT IN and NULL values

This is the big one.

If the list (or subquery results) contains NULL, NOT IN can behave in a surprising way. In SQL, comparisons with NULL produce “unknown,” not true or false.

Example problem:

SELECT customer_id
FROM customers
WHERE customer_idNOTIN (
SELECT customer_id
FROM orders
);

If the orders.customer_id column contains even one NULL, then the subquery result list includes NULL, and the NOT IN comparison may return zero rows.

That can make you stare at your screen like: “I know there are customers without orders. Where did they go?”

How to fix it

Filter out NULL inside the subquery:

SELECT customer_id, full_name
FROM customers
WHERE customer_idNOTIN (
SELECT customer_id
FROM orders
WHERE customer_idISNOT NULL
);

Now the comparison stays clean.

NOT IN vs NOT EXISTS

Sometimes NOT EXISTS is safer than NOT IN, especially with subqueries that might return NULL.

Here’s the same “customers with no orders” query using NOT EXISTS:

SELECT c.customer_id, c.full_name
FROM customers c
WHERENOTEXISTS (
SELECT1
FROM orders o
WHERE o.customer_id= c.customer_id
);

Many developers prefer this in production because it avoids the NULL trap and often performs well in real databases.

NOT IN vs <> (not equal)

NOT IN is for excluding multiple values.

<> is for excluding just one value.

Example:

-- Excluding one status
SELECT*
FROM orders
WHERE status<>'Canceled';

If you need to exclude two or more values, NOT IN becomes the cleaner option:

SELECT*
FROM orders
WHERE statusNOTIN ('Canceled','Refunded');

Using NOT IN with strings, numbers, and IDs

The NOT IN operator works across data types:

-- Numbers
WHERE ratingNOTIN (1,2)

-- Strings
WHERE countryNOTIN ('US','CA')

-- IDs
WHERE user_idNOTIN (10,15,22)

Just match the types to the column. Mixing types can cause implicit conversions or errors depending on the database.

A small performance tip

If your list is huge (like hundreds or thousands of values), NOT IN (...) can get messy and slow.

In that case, your best option is often:

• put those values into a table (or temp table)
• join and filter them out cleanly

That’s more scalable than pasting a novel inside parentheses.

Summary

The NOT IN operator helps you filter out rows that match a list of excluded values or a subquery result. Use it for clean reporting, removing unwanted categories, and finding “missing” relationships between tables. Just watch out for NULL values in subqueries, because a single NULL can break the whole filter, and consider NOT EXISTS as a safer alternative in those cases.