SQL NOT

What is the SQL NOT operator?

SQL NOT negates a boolean condition, returning TRUE when the condition is FALSE and vice-versa.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL NOT: PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, SQLite, Snowflake, Redshift, BigQuery, DB2, Teradata

SQL NOT Full Explanation

SQL NOT is a unary logical operator that inverts the truth value of the predicate that follows it. It supports three-valued logic, so NOT TRUE returns FALSE, NOT FALSE returns TRUE, and NOT UNKNOWN (NULL) remains UNKNOWN. NOT can precede any boolean expression or keyword predicate such as IN, BETWEEN, LIKE, or EXISTS, creating forms like NOT IN or NOT EXISTS. Operator precedence places NOT above AND and OR, so NOT condition AND other_condition is evaluated as (NOT condition) AND other_condition unless parentheses change the order. NOT is valid in SELECT, UPDATE, DELETE, MERGE, HAVING, CHECK constraints, CASE expressions, and anywhere a boolean expression is allowed. An expression that evaluates to NULL remains NULL after NOT, which can affect filter results in unexpected ways. Most SQL dialects treat NOT as a reserved keyword.

SQL NOT Syntax

-- Basic form
NOT boolean_expression;

-- In a WHERE clause
SELECT column_list
FROM table_name
WHERE NOT condition;

-- Common patterns
predicate NOT IN (value_list)
predicate NOT BETWEEN low AND high
predicate NOT LIKE pattern
NOT EXISTS (subquery)

SQL NOT Parameters

Example Queries Using SQL NOT

-- 1. Negate a simple boolean column
SELECT *
FROM employees
WHERE NOT active;

-- 2. Exclude shipped or delivered orders
SELECT *
FROM orders
WHERE status NOT IN ('shipped', 'delivered');

-- 3. Find customers with no orders
SELECT c.customer_id
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.customer_id
);

-- 4. Products outside a price band
SELECT *
FROM products
WHERE price NOT BETWEEN 10 AND 50;

-- 5. Users with non-company email addresses
SELECT *
FROM users
WHERE email NOT LIKE '%@example.com';

Expected Output Using SQL NOT

Each query returns only rows where the specified predicate evaluates to FALSE (or UNKNOWN) after applying NOT, effectively filtering out the rows that matched the original condition

Use Cases with SQL NOT

Exclude inactive or false boolean flags
Filter out specific values or ranges (NOT IN, NOT BETWEEN)
Match strings that do not fit a pattern (NOT LIKE)
Identify rows that lack related records (NOT EXISTS)
Invert complex boolean logic in validation, data quality checks, and test datasets

Common Mistakes with SQL NOT

Forgetting operator precedence and expecting NOT to apply after AND/OR without parentheses
Assuming NOT NULL is valid syntax in a WHERE clause (use IS NOT NULL instead)
Overlooking that NOT UNKNOWN (NULL) yields UNKNOWN, so rows with NULL values may not appear
Writing NOT = instead of <> or !=
Assuming NOT LIKE is the same as LIKE NOT, which is invalid

First Introduced In

SQL-86 (ANSI X3.135-1986)

Frequently Asked Questions

What is the difference between NOT IN and NOT EXISTS?

NOT IN evaluates the right-hand list once and fails if any NULL is present, potentially returning no rows. NOT EXISTS checks each candidate row individually and is generally safer with NULLs and correlated subqueries.

How does NOT interact with NULL values?

SQL uses three-valued logic. If the operand of NOT is NULL (UNKNOWN), the result remains UNKNOWN. To ensure rows with NULLs are included or excluded, combine NOT with IS NULL or IS NOT NULL tests.

Does NOT have higher precedence than AND and OR?

Yes. NOT is evaluated before AND, which is evaluated before OR. Use parentheses to override default precedence and to make complex logic more readable.

Can NOT be used in CHECK constraints?

Absolutely. A CHECK (NOT condition) constraint enforces that the condition is false for every row inserted or updated in the table.