SQL guide · Filtering

SQL NOT: Reverse a Condition

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

Browse all SQL guides

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