NOT Operator in SQL
Introduction
The NOT operator negates a SQL condition. It changes TRUE to FALSE and FALSE to TRUE, allowing a query to describe exclusions such as customers without orders, products outside a range, email addresses that do not match an internal domain, or records that are not in an allowed state.
SELECT customer_id, display_name, status
FROM customers
WHERE NOT (status = 'ACTIVE');
Negation looks simple, but SQL also has UNKNOWN because of NULL. NOT UNKNOWN remains UNKNOWN, so NOT does not automatically return every row omitted by the positive condition. Reliable negative logic requires explicit NULL behavior, careful handling of NOT IN, readable grouping, secure allowlists, and realistic performance tests.
Basic NOT Syntax
NOT appears before the predicate or grouped expression being negated.
SELECT column_list
FROM table_name
WHERE NOT condition;
Parentheses make the target of negation clear, especially for a compound expression. NOT can be combined with equality, ranges, membership, patterns, existence, NULL predicates, AND, and OR.
NOT Truth Table
| Condition | NOT condition |
|---|---|
| TRUE | FALSE |
| FALSE | TRUE |
| UNKNOWN | UNKNOWN |
The third row is essential. SQL cannot turn unknown information into a known opposite merely by negating it. WHERE keeps only TRUE, so both FALSE and UNKNOWN rows are removed.
NOT with Equality
Negating equality selects known values that are not equal.
WHERE NOT (status = 'ACTIVE')
For one comparison, the direct not-equal operator is usually clearer.
WHERE status <> 'ACTIVE'
Both forms exclude NULL because the comparison becomes UNKNOWN. Use NOT around a group when the negation itself communicates the rule better than rewriting every individual comparison.
Direct Comparison Rewrites
| Negated form | Clear direct form for known values |
|---|---|
NOT (x = 10) | x <> 10 |
NOT (x > 10) | x <= 10 |
NOT (x >= 10) | x < 10 |
NOT (x < 10) | x >= 10 |
NOT (x <= 10) | x > 10 |
These transformations preserve UNKNOWN for NULL, so neither form includes missing values. Choose the form that states the requirement most directly and makes boundaries visible.
NOT Does Not Include NULL Automatically
A nullable status that is neither known ACTIVE nor known non-ACTIVE is missing information. The following query omits NULL.
SELECT customer_id, status
FROM customers
WHERE status <> 'ACTIVE';
If the business rule treats a missing status as non-active, include it explicitly.
WHERE status <> 'ACTIVE'
OR status IS NULL
Another policy may reject unknown status. NULL inclusion is a domain choice rather than a universal correction.
Why UNKNOWN Exists
NULL represents missing, unavailable, or inapplicable information. The database cannot know whether a missing salary exceeds a threshold or whether a missing status equals ACTIVE. Ordinary comparison therefore yields UNKNOWN.
NOT negates the truth result but cannot invent the absent fact. This differs from many programming environments where null checks are handled with two-valued boolean expressions. SQL predicates must account for UNKNOWN deliberately.
NOT with AND: De Morgan's Law
For classical known values, NOT applied to an AND group corresponds to OR between the negated parts.
NOT (status = 'ACTIVE' AND email_verified = TRUE)
-- Conceptual rewrite:
status <> 'ACTIVE' OR email_verified <> TRUE
The rule says a record fails the combined requirement when at least one mandatory part fails. Nullable expressions can remain UNKNOWN, so test NULL before relying on an algebraic rewrite.
NOT with OR: De Morgan's Law
NOT applied to an OR group corresponds to AND between negated parts.
NOT (status = 'CANCELLED' OR status = 'FAILED')
-- Clear membership form:
status NOT IN ('CANCELLED', 'FAILED')
The record must be neither cancelled nor failed. For nullable status, both forms remain UNKNOWN when status is NULL. Add an explicit NULL rule if required.
NOT and Operator Precedence
NOT normally has higher precedence than AND, which has higher precedence than OR. Parentheses should still show exactly which expression is negated.
WHERE tenant_id = :tenant_id
AND NOT (status = 'CANCELLED' OR status = 'FAILED')
AND deleted_at IS NULL
Without grouping, a future edit can easily negate only one comparison or let an OR branch escape mandatory scope. Readability is a correctness mechanism.
NOT with IN
NOT IN excludes values found in a list or subquery.
SELECT order_id, status
FROM orders
WHERE status NOT IN ('CANCELLED', 'FAILED', 'VOID');
This is clearer than three not-equal conditions joined by AND. It excludes NULL status because membership is UNKNOWN. Use an explicit OR branch only when missing status should qualify.
The NOT IN and NULL Problem
NOT IN can return no rows unexpectedly when its set contains NULL. For each candidate, SQL must prove that it differs from every set value. A comparison against NULL is UNKNOWN, so the final predicate may never become TRUE.
-- Risky if excluded_customers.customer_id can be NULL.
WHERE c.customer_id NOT IN (
SELECT customer_id
FROM excluded_customers
)
A NOT NULL constraint on the subquery key helps, but anti-existence is often expressed more safely with NOT EXISTS.
NOT EXISTS for Anti-Joins
NOT EXISTS selects a row when no related row satisfies the correlated subquery.
SELECT c.customer_id, c.display_name
FROM customers AS c
WHERE c.tenant_id = :tenant_id
AND NOT EXISTS (
SELECT 1
FROM orders AS o
WHERE o.tenant_id = c.tenant_id
AND o.customer_id = c.customer_id
AND o.status = 'COMPLETED'
);
This finds tenant customers without a completed order. NULL values selected inside an EXISTS subquery do not create the NOT IN problem because existence concerns matching rows, not value equality across a list.
NOT EXISTS with Business Filters
The subquery must represent the exact relationship whose absence matters. "Customers without active subscriptions" requires subscription ownership, current state, and time validity inside the subquery.
WHERE c.tenant_id = :tenant_id
AND NOT EXISTS (
SELECT 1
FROM subscriptions AS s
WHERE s.tenant_id = c.tenant_id
AND s.customer_id = c.customer_id
AND s.starts_at <= :as_of_time
AND (s.ends_at > :as_of_time OR s.ends_at IS NULL)
)
Missing one condition changes what "without" means. Test a customer with expired, future, cancelled, and active subscriptions separately.
NOT IN versus NOT EXISTS
NOT IN compares one value with a set and is concise when the set is small or guaranteed non-NULL. NOT EXISTS tests absence of a related match and handles nullable subquery values more predictably. Neither is universally faster.
The optimizer may transform both into anti-join plans. Choose based on semantics first, enforce key nullability, inspect actual plans, and measure representative data before making performance claims.
NOT BETWEEN and Range Boundaries
BETWEEN is inclusive on both ends. NOT BETWEEN is true for known values below the lower boundary or above the upper boundary.
WHERE price NOT BETWEEN 100.00 AND 500.00
-- Equivalent for known values:
WHERE price < 100.00 OR price > 500.00
Prices exactly 100.00 and 500.00 are excluded by NOT BETWEEN because they lie inside the inclusive range. NULL remains UNKNOWN. Explicit comparisons can be clearer when boundaries are mixed or when timestamp intervals are half-open.
NOT LIKE and Pattern Exclusion
NOT LIKE excludes known text that matches a pattern.
SELECT user_id, email
FROM users
WHERE email NOT LIKE '%@internal.example';
NULL email is omitted because pattern comparison becomes UNKNOWN. Add OR email IS NULL only when missing addresses should belong to the result. Define case sensitivity, escape wildcards, and avoid using a broad negative pattern as the only security control.
NOT with IS NULL
NOT (column IS NULL) is logically the same as column IS NOT NULL. The direct form is clearer.
WHERE processed_at IS NOT NULL
Likewise, NOT (column IS NOT NULL) simplifies to IS NULL. Prefer standard positive predicates over unnecessary negation, especially in long conditions.
NOT in WHERE, HAVING, and CASE
WHERE uses NOT to exclude rows, HAVING excludes groups based on aggregate logic, and CASE can classify records whose condition is not true.
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING NOT (COUNT(*) BETWEEN 1 AND 4);
This retains known counts outside the inclusive range, though a direct COUNT(*) = 0 OR COUNT(*) >= 5 may express the requirement better depending on grouping. CASE branches need explicit NULL handling if unknown conditions deserve a separate label.
NOT in CHECK Constraints
NOT can prohibit invalid combinations, but a positive allowed-state constraint is often easier to audit.
CHECK (NOT (status = 'SHIPPED' AND shipped_at IS NULL))
This says a shipped record cannot lack shipped_at. The equivalent conditional form status <> 'SHIPPED' OR shipped_at IS NOT NULL may be easier to explain. Because CHECK commonly accepts UNKNOWN, combine comparisons with NOT NULL when missing data must be rejected.
Negative Logic versus Positive Allowlists
A denylist says everything is allowed except known disallowed states. This becomes unsafe when new states are added. A positive allowlist says exactly which states are permitted.
-- Broad denylist: a future state may pass unintentionally.
WHERE status <> 'CANCELLED'
-- Explicit allowlist for this operation.
WHERE status IN ('PENDING', 'PAID')
Use positive allowlists for authorization, workflows, and destructive operations whenever the permitted set is known. Negative predicates are useful for reports and exception detection but may be too broad for commands.
NOT in Authorization
Security rules such as "role is not GUEST" are usually weaker than an explicit role allowlist. A future role might gain access accidentally.
WHERE tenant_id = :tenant_id
AND role_code IN ('ADMIN', 'EDITOR')
AND account_status = 'ACTIVE'
Do not accept role, tenant, or ownership values from request fields as trusted policy. Derive them from authenticated context and enforce least privilege. NOT is not a substitute for a complete authorization model.
NOT and Multi-Tenant Security
Negative filters must remain subordinate to positive tenant scope. A query for non-archived documents still needs an exact authenticated tenant condition.
WHERE tenant_id = :authenticated_tenant_id
AND document_id = :document_id
AND status <> 'ARCHIVED'
AND deleted_at IS NULL
Test another tenant's record that satisfies every negative filter. It must remain inaccessible. Security is defined by positive identity and scope, not merely by excluding a few known bad states.
NOT in State Transitions
A transition guarded with status <> 'SHIPPED' may unintentionally allow CANCELLED, FAILED, REFUNDED, or future states. Use the explicit source states from which the transition is valid.
UPDATE orders
SET status = 'SHIPPED',
shipped_at = CURRENT_TIMESTAMP
WHERE order_id = :order_id
AND tenant_id = :tenant_id
AND status IN ('PAID', 'PACKED');
Affected-row validation then detects races or invalid state. Positive state machines remain stable as new states are introduced.
NOT in UPDATE and DELETE
Negative mutation predicates can select a much broader set than intended. An update of every row not equal to one status includes all other known statuses, including newly introduced ones.
Prefer identity plus positive eligibility in commands. For administrative maintenance that genuinely needs a negative filter, preview the same predicate with SELECT, count targets, inspect representative rows, use bounded transactions, and verify the affected count.
A Safer Destructive Query
-- Broad negative condition.
DELETE FROM sessions
WHERE status <> 'ACTIVE';
-- Explicit cleanup states and time boundary.
DELETE FROM sessions
WHERE tenant_id = :tenant_id
AND status IN ('EXPIRED', 'REVOKED')
AND ended_at < :retention_cutoff;
The positive cleanup query communicates policy and avoids deleting a future state accidentally. Large cleanups should use indexed batches and a tested recovery plan.
NOT for Exception Reports
Negative logic is natural for finding exceptions: products never sold, employees without managers, orders without successful payments, or records outside valid ranges. Reports can tolerate broader negative questions when their semantics are explicit.
SELECT p.product_id, p.product_name
FROM products AS p
WHERE p.tenant_id = :tenant_id
AND NOT EXISTS (
SELECT 1
FROM order_items AS i
WHERE i.product_id = p.product_id
);
Define whether cancelled orders, test data, historical tenants, and soft-deleted products count. Absence is meaningful only relative to a precisely scoped relationship.
NOT for Temporal Exclusion
"Not within the maintenance window" negates a time range. For a half-open window, the direct outside-range expression is often clearest.
WHERE event_time < :window_start
OR event_time >= :window_end
NULL event_time remains UNKNOWN. If missing time means outside, include it explicitly; if missing time is invalid, enforce NOT NULL. For recurring windows and time zones, calculate boundaries using documented business rules.
NOT and Boolean Columns
A nullable boolean has TRUE, FALSE, and NULL stored states. NOT is_verified selects known FALSE in products with native boolean syntax but not NULL. If the field is logically two-state, define it NOT NULL and use a default when appropriate.
If NULL means "not yet assessed," model and query it as a distinct domain state. Do not silently merge unknown with false unless the business explicitly treats them the same.
NOT Does Not Guarantee Evaluation Order
NOT is logical, not procedural. The optimizer can transform or reorder predicates, and it need not evaluate a positive guard before a negated expression. Make every expression safe for any candidate row.
Use valid data types, constraints, CASE or NULLIF where appropriate, and safe conversion functions supported by the database. Do not rely on observed short-circuit behavior in one execution plan.
NOT and Query Performance
Negative predicates often match a large share of a table, so a scan may be cheaper than an index lookup. status <> 'ARCHIVED' can return nearly everything when only a few rows are archived. That does not make NOT inherently slow; selectivity and access paths matter.
NOT EXISTS may produce an efficient anti-join when relationship indexes exist. NOT IN can also be efficient when semantics and nullability are safe. Inspect actual plans, row estimates, and rows examined rather than selecting an operator by folklore.
NOT, Indexes, and Sargability
Direct negative comparisons may use indexes, but broad results reduce their value. Wrapping the column in a function can further limit searchable access.
-- Potentially less searchable.
WHERE NOT (LOWER(email) LIKE '%@internal.example')
-- Consider normalized data or a suitable indexed search design.
Positive flags or status allowlists can sometimes be more selective. Use expression indexes, normalized columns, or full-text facilities only when they match real query semantics and measured workload.
NOT and Partition Pruning
A negative condition on a partition key may require many partitions because it describes everything except one value or range. Positive bounded ranges often allow clearer pruning.
For time-partitioned maintenance, identify exact partitions or use direct start and end boundaries instead of negating a date function. Confirm pruning in the execution plan; syntactic appearance does not prove physical exclusion.
NOT and Parameterization
Bind excluded values as typed parameters. Never concatenate untrusted text into a NOT, NOT IN, or NOT LIKE clause. Values remain parameters, while optional columns, operators, and sort choices must come from strict server-side allowlists.
Parameterized SQL protects the logical structure from injection, but it does not fix a broad negative rule. Authorization, tenant scope, and approved state lists still require correct design.
Dynamic Exclusion Filters
Search APIs may allow users to exclude selected statuses, categories, or tags. Validate list length and values, bind each value safely, and define what an empty list means. Generating NOT IN () is invalid in many systems, while omitting the condition can unintentionally broaden results.
Keep mandatory tenant and visibility filters independent of optional exclusions. Apply page limits and monitor broad exclusion queries because "everything except" can return a large data set.
NOT in Audit and Compliance Queries
Audit queries often look for missing evidence: privileged actions without approval, payments without reconciliation, or deployments without a change ticket. NOT EXISTS is appropriate when absence of a related record defines the exception.
Scope both outer and inner records by tenant, environment, event type, and time window. Record retention can make an old related event absent even though it originally existed, so the query's time horizon must match retention policy.
Avoiding Double and Triple Negatives
Expressions such as "not inactive," "not without approval," or "not not verified" force reviewers to mentally invert the rule several times. They increase the chance that NULL, a future state, or one parenthesis changes the intended meaning. Replace them with a direct positive predicate whenever the domain provides one.
-- Difficult to reason about.
WHERE NOT (status <> 'ACTIVE')
AND NOT (approved_at IS NULL)
-- Direct and reviewable.
WHERE status = 'ACTIVE'
AND approved_at IS NOT NULL
Sometimes a double negative reflects weak naming in the schema, such as is_not_disabled. Prefer affirmative column names and controlled statuses. Clear naming reduces logical complexity in every query, constraint, report, and API.
NOT with Aggregates
Aggregate exclusion belongs in HAVING because it applies after groups are formed. A report can select customers whose completed-order count is not within a normal band.
SELECT customer_id, COUNT(*) AS completed_orders
FROM orders
WHERE status = 'COMPLETED'
GROUP BY customer_id
HAVING COUNT(*) NOT BETWEEN 1 AND 4;
COUNT returns a known non-NULL number, so NOT BETWEEN has straightforward boundaries here. For SUM or AVG, empty groups and nullable inputs require more care. State the desired groups positively if that form is easier to review, and verify whether zero activity is represented by an absent group requiring an outer join.
NOT with Pagination and Ordered Results
Exclusion filters can be combined with pagination, but the order must remain deterministic. A feed excluding archived items still needs a unique tie-breaker.
SELECT item_id, created_at, status
FROM feed_items
WHERE tenant_id = :tenant_id
AND status <> 'ARCHIVED'
AND status IS NOT NULL
AND (created_at, item_id) < (:cursor_time, :cursor_id)
ORDER BY created_at DESC, item_id DESC
FETCH FIRST :page_size ROWS ONLY;
If new statuses appear, the negative filter admits them automatically. For a user-facing feed, an explicit visible-status allowlist may be safer. Test cursor boundaries with excluded rows between included records to ensure pages neither repeat nor skip eligible items.
Data Modeling Can Reduce Negative Logic
Frequent complex NOT predicates may reveal a model that does not represent the positive business fact directly. If every query derives "available" from not deleted, not blocked, not expired, and not out of stock, a documented status or view may make the concept clearer. The derived value must remain consistent with its source facts.
Do not add flags merely to avoid SQL complexity; redundant state can drift. Instead, define one authoritative model, enforce valid transitions, and expose well-named views or repository methods for recurring concepts. Constraints and generated values can help when the database supports the required rule safely.
Monitoring Negative Queries in Production
Monitor negative-query latency, rows examined, rows returned, anti-join cardinality, temporary work, and plan changes. A predicate that excludes only one percent of a table still returns ninety-nine percent, which can overwhelm network and application processing even if the scan itself is efficient.
For NOT EXISTS, verify indexes on correlation keys and important inner filters. Watch for data growth that changes absence rates. A report of customers without orders may initially return few rows but become large after an import or retention purge. Result limits, background exports, and workload scheduling are part of production design.
Translating Negative Requirements
Before writing NOT, identify the positive condition being negated and list the states that should pass, fail, or remain unknown. "Not cancelled" may mean every state except CANCELLED, or it may mean only PENDING and PAID are actionable. Those are different requirements.
Create examples for current and future states, NULL, soft-deleted records, another tenant, and expired historical data. If the valid set can be enumerated, prefer a positive allowlist. Use true negative logic when absence or exclusion is the domain question, such as "no successful payment exists" or "outside the maintenance window."
Practical Production Examples
-- Orders not in terminal states, excluding NULL deliberately.
WHERE status NOT IN ('DELIVERED', 'CANCELLED', 'REFUNDED')
AND status IS NOT NULL
-- Products outside an allowed price band.
WHERE price < :minimum_price
OR price > :maximum_price
-- Customers without a successful payment.
WHERE NOT EXISTS (
SELECT 1 FROM payments AS p
WHERE p.customer_id = c.customer_id
AND p.status = 'SUCCEEDED'
)
-- Exclude test records using a positive production flag.
WHERE environment = 'PRODUCTION'
The final example shows that a positive requirement is often clearer than a negative test-name pattern. Choose stable domain facts over formatting conventions.
Testing NOT Conditions
Test the positive condition as TRUE, FALSE, and UNKNOWN, then verify its negation. For not-equal, include equal, different, and NULL values. For NOT BETWEEN, test below, both boundaries, inside, above, and NULL. For NOT LIKE, test a match, nonmatch, case variations, wildcard characters, empty text, and NULL.
For complex groups, build a truth table covering each AND and OR combination before applying NOT. Test the original and rewritten De Morgan forms against the same nullable dataset to prove equivalence for the intended domain.
Testing NOT IN and NOT EXISTS
NOT IN tests must include an empty set, matching value, nonmatching values, NULL outer value, and NULL inside the list or subquery. Verify documented behavior rather than assuming the result.
NOT EXISTS tests should cover no related rows, unrelated rows, one matching row, several matching rows, nullable columns, other tenants, soft-deleted relationships, and time-expired relationships. Existence depends on every condition inside the subquery.
Security and Mutation Testing
Create future or unexpected states to prove a denylist does not grant access or permit transitions accidentally. Compare a negative role filter with an explicit role allowlist. Include another tenant's row that passes every negative condition but must remain inaccessible.
For UPDATE and DELETE, preview exact targets, assert affected-row counts, simulate concurrent state changes, and verify rollback. A negative predicate that was safe on today's statuses can become unsafe after a deployment adds another status.
Common NOT Mistakes
- Assuming NOT automatically includes NULL values.
- Writing
NOT column = NULLinstead of IS NOT NULL. - Using NOT IN with a nullable list or subquery without analysis.
- Guarding workflows with broad negative states instead of positive allowlists.
- Using a negative role filter as authorization.
- Applying broad NOT predicates in UPDATE or DELETE without preview.
- Writing double or triple negatives that obscure the policy.
- Negating a complex expression without visible parentheses.
- Assuming NOT provides procedural evaluation order.
- Using NOT LIKE as a general search strategy on large tables.
- Assuming NOT EXISTS is always faster or NOT IN is always wrong.
- Ignoring retention, tenant, and soft-delete scope in anti-joins.
NOT Operator Review Checklist
- State the positive condition before deciding to negate it.
- Prefer direct operators or positive allowlists when they are clearer.
- Define how NULL and UNKNOWN should behave explicitly.
- Use NOT EXISTS for relationship absence when nullable sets are possible.
- Verify every boundary in NOT BETWEEN and outside-range logic.
- Parenthesize compound expressions being negated.
- Keep tenant, ownership, and visibility scope positive and mandatory.
- Avoid broad negative guards in workflow transitions and destructive commands.
- Parameterize values and allowlist dynamic SQL structure.
- Test future states, empty sets, NULLs, and duplicate relationships.
- Inspect plans and selectivity under production-like data.
- Document retention and time scope for absence-based reports.
Interview Questions and Answers
What does NOT do in SQL? It negates a condition: TRUE becomes FALSE, FALSE becomes TRUE, and UNKNOWN remains UNKNOWN.
Why does NOT not include NULL automatically? A comparison with NULL is UNKNOWN, and NOT UNKNOWN is still UNKNOWN.
Is NOT (x = 10) the same as x <> 10? They behave the same for known values and both exclude NULL.
Why is NOT IN with NULL dangerous? A NULL in the set can make the predicate UNKNOWN and unexpectedly return no rows.
What does NOT EXISTS mean? No related row satisfies the correlated subquery.
When is NOT EXISTS preferable to NOT IN? For anti-relationships, especially when the compared set can contain NULL.
What is NOT BETWEEN? For known values, it selects values below the inclusive lower boundary or above the inclusive upper boundary.
Why prefer positive conditions? Allowlists are easier to read and do not automatically admit future states.
Is NOT always slow? No. Performance depends on selectivity, indexes, statistics, and optimizer choices.
How should NOT be tested? Test TRUE, FALSE, UNKNOWN, boundaries, empty and nullable sets, security scope, future states, and mutation targets.
Key Takeaway
The SQL NOT operator expresses exclusion and absence, but negation does not turn missing information into a known opposite. Correct use depends on three-valued logic, explicit NULL policy, safe anti-joins, readable grouping, positive security scope, and careful treatment of future states.
RELIABLE NOT = CLEAR POSITIVE CONDITION
+ EXPLICIT NEGATION SCOPE
+ DEFINED NULL BEHAVIOR
+ SAFE NOT IN OR NOT EXISTS
+ POSITIVE SECURITY ALLOWLISTS
+ GUARDED MUTATIONS
+ MEASURED SELECTIVITY
+ COMPLETE EXCLUSION TESTS
Use NOT where exclusion is the real business question, not where a positive rule would be clearer. Test unknown values and new states, and never let negative logic replace explicit authorization or mutation eligibility.