Suggest an edit

Improve this article

Refine the answer for “What does NOT IN do?”. Your changes go to moderation before they’re published.

Approval required

Content

What you’re changing

Title (EN)

Short answer (EN)

Shown above the full answer for quick recall.

Answer (EN)

`NOT IN` is an operator that **excludes** rows if a column's value is in the given list.
It's the opposite of `IN`.

### How it works

`NOT IN` checks that a value **doesn't match any** element from the list.
If even one match is found, the row is excluded from the result.

```sql
SELECT *
FROM employees
WHERE department NOT IN ('HR', 'Finance', 'Legal');
```

This query selects every employee **except** those working in the HR, Finance, or Legal departments.

### The same thing without `NOT IN`

Without it, you'd have to write:

```sql
WHERE department <> 'HR'
  AND department <> 'Finance'
  AND department <> 'Legal'
```

`NOT IN` does the same thing, but simpler and more readable.

### It can be used with a subquery

```sql
SELECT *
FROM customers
WHERE id NOT IN (SELECT customer_id FROM blacklist);
```

Selects every customer **not present** in the `blacklist` table.

### Important

If a `NULL` value shows up in the `NOT IN` list,
the result can end up **empty**, because SQL doesn't know whether to count `NULL` as a match.
To avoid this, it's worth excluding `NULL` from the subquery before filtering:

```sql
WHERE id NOT IN (SELECT customer_id FROM blacklist WHERE customer_id IS NOT NULL);
```

**Summary:**
`NOT IN` filters data, **removing every row whose value is in the given list**.
It's used for exclusions and "blacklists" in queries.

Markdown · drag & drop images · ⌘B / ⌘I shortcuts229 words

For the reviewer

Note to the moderator (optional)

Visible only to the moderator. Helps review go faster.