# SQL NOT: Reverse a Condition

- URL: https://betteranalyst.com/sql/not

SQL guide · Filtering

The `NOT` operator reverses the result of a boolean condition. A row that would have matched is excluded, and a row that would have been excluded is returned, with one important exception: rows for which the condition is unknown stay unknown and are still dropped.

`NOT` appears in several familiar forms. You can negate a whole expression with `NOT (a AND b)`, or use the paired predicates `NOT IN`, `NOT LIKE`, `NOT BETWEEN`, `IS NOT NULL` and `NOT EXISTS`. Each of these is the inverse of its positive counterpart and works the same way in `WHERE`, `HAVING` and `ON`.

Negative conditions are easy to get subtly wrong because of NULL handling. Knowing that `NOT` of unknown is still unknown explains most surprising results, particularly with `NOT IN` when a subquery returns a NULL.

## Basic syntax

```
SELECT column1
FROM table_name
WHERE NOT condition;

WHERE column NOT IN (v1, v2)
WHERE column NOT LIKE 'pattern'
WHERE column NOT BETWEEN a AND b
WHERE column IS NOT NULL
WHERE NOT EXISTS (subquery)
```

`NOT` has higher precedence than `AND` and `OR`, so `NOT a AND b` means `(NOT a) AND b`. Wrap the full condition in parentheses if you want to negate a combination. When in doubt, test the negated condition on a few rows that include NULL values, since that is where a negation most often disagrees with intuition. Writing the positive condition first and then adding `NOT` keeps the logic easy to review.

## SQL NOT examples

### NOT with a comparison

This excludes one country. It is the same as `country <> 'France'`, but `NOT` can wrap more complex expressions where an inverted operator is awkward.

```
SELECT customer_id, name, country
FROM customers
WHERE NOT country = 'France';
```

### NOT IN a list

Returns products outside a set of categories. The list contains literals, so there are no NULL concerns here.

```
SELECT product_id, name, category
FROM products
WHERE category NOT IN ('Books', 'Games', 'Music');
```

### NOT LIKE and NOT BETWEEN

These predicates exclude a pattern or a range. This finds orders outside the first quarter whose reference does not start with the test prefix.

```
SELECT order_id, reference, order_date
FROM orders
WHERE reference NOT LIKE 'TEST%'
  AND order_date NOT BETWEEN '2026-01-01' AND '2026-03-31';
```

### NOT EXISTS for rows without a match

This returns customers who have never placed an order. `NOT EXISTS` is safe with NULLs, unlike `NOT IN` against a subquery that might yield NULL.

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

## Where SQL dialects differ

**PostgreSQL:** Supports `IS DISTINCT FROM` and `IS NOT DISTINCT FROM` for NULL-safe comparison, which avoids many negation pitfalls. `NOT ILIKE` handles case-insensitive exclusion.

**MySQL:** Accepts `!` as a synonym for `NOT`, which is deprecated in newer releases, so use the keyword. `<=>` is a NULL-safe equality operator, and `NOT (a <=> b)` is the NULL-safe inequality.

**SQL Server:** No boolean type, so `NOT` applies only to predicates, as in `NOT (qty > 5)`. For a bit column write `is_active = 0` rather than `NOT is_active`.

**SQLite:** Supports `IS NOT` as a NULL-safe inequality, so `a IS NOT b` is true when exactly one is NULL. Non-zero numbers are true.

## Common mistakes

- Using `NOT IN (SELECT ...)` when the subquery can return NULL. The whole predicate becomes unknown and zero rows return; use `NOT EXISTS` or filter NULLs out.
- Expecting `NOT col = value` to return rows where `col` is NULL. They are unknown, not true, and are excluded.
- Forgetting precedence in `NOT a AND b`, which negates only `a`. Add parentheses to negate the pair.
- Writing `WHERE col NOT NULL`. The correct form is `IS NOT NULL`.

## Related SQL guides

- [SQL WHERE](https://betteranalyst.com/sql/where)
- [SQL AND](https://betteranalyst.com/sql/and)
- [SQL IN](https://betteranalyst.com/sql/in)
- [SQL LIKE](https://betteranalyst.com/sql/like)
- [SQL IS NULL](https://betteranalyst.com/sql/is-null)
- [SQL EXISTS](https://betteranalyst.com/sql/exists)

[Browse all SQL guides](https://betteranalyst.com/sql)

## Need help writing the SQL?

Describe your tables and the result you want, then review the generated query against your database before running it.

[Try the AI SQL generator](https://betteranalyst.com/ai-sql-query-generator)