Database Management SystemsSQL Queries

Logical Operators

LevelBeginner

Duration55 mins

TopicSQL Queries

3 / 5

NOT Operator

The Power of Negation

Sometimes the clearest way to express what you want is by specifying what you don't want. A moderation system finds all posts that are NOT approved. An inventory query retrieves products that are NOT in the Electronics category. A compliance report identifies transactions that are NOT flagged as reviewed.

The NOT operator provides this capability through logical negation—inverting the truth value of any condition. While conceptually simple, NOT has subtle behaviors with NULL values and powerful combinations with other SQL constructs like IN, BETWEEN, and LIKE.

Mastering NOT enables you to express exclusionary logic clearly and understand how negation propagates through complex expressions.

What You Will Learn

By the end of this page, you will master the NOT operator: its inversion semantics, critical behavior with NULL (where NOT UNKNOWN = UNKNOWN), combinations with IN/BETWEEN/LIKE/EXISTS, De Morgan's Laws for negating compound expressions, and common patterns for exclusionary queries.

The NOT Operator Fundamentals

The NOT operator performs logical negation—a fundamental unary operation from Boolean algebra. Unlike AND and OR which combine two operands, NOT operates on a single operand and inverts its truth value.

Formal Definition:

NOT condition → TRUE if condition is FALSE
NOT condition → FALSE if condition is TRUE

Key Characteristics:

Unary operator: Takes one operand (unlike binary AND/OR)
Prefix placement: Appears before the condition it negates
Highest precedence: Evaluated before AND and OR
Self-inverse: NOT (NOT condition) = condition (double negation)

basic_not_syntax.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- Basic NOT Syntax
SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;
 
-- Example: Find products that are NOT in Electronics category
SELECT product_id, name, category
FROM products
WHERE NOT category = 'Electronics';
 
-- Equivalent using !=
SELECT product_id, name, category
FROM products
WHERE category != 'Electronics';
 
-- NOT with comparison operators
SELECT order_id, status
FROM orders
WHERE NOT status = 'Completed';  -- Same as status != 'Completed'
 
-- NOT inverting a compound expression
SELECT employee_id, name
FROM employees
WHERE NOT (department = 'Sales' AND salary > 50000);
 
-- Double negation (equals original)
SELECT * FROM products
WHERE NOT NOT (price > 100);  -- Same as: WHERE price > 100

NOT vs Comparison Operators

For simple equality negation, use != or <> directly rather than NOT column = value. It's clearer and equivalent. Reserve NOT for negating compound expressions, or when combining with IN, BETWEEN, LIKE, EXISTS, and NULL checks where NOT is required or clearer.

Truth Table Analysis

NOT has the simplest truth table of the logical operators—it simply inverts its operand:

NOT Truth Table (Two-Valued Logic)
Condition	NOT Condition
TRUE	FALSE
FALSE	TRUE

Three-Valued Logic Extension:

In SQL's three-valued logic with NULL, the NOT truth table extends to include UNKNOWN:

NOT Truth Table with Three-Valued Logic
Condition	NOT Condition	Explanation
TRUE	FALSE	Inversion of TRUE
FALSE	TRUE	Inversion of FALSE
UNKNOWN	UNKNOWN	Cannot invert unknown value

NOT UNKNOWN = UNKNOWN

This is the critical insight for NOT: negating an unknown value produces an unknown value. If we don't know whether something is true, we also don't know whether its negation is true. This has profound implications for NULL handling.

NOT and NULL Values

The behavior of NOT with NULL is one of the most counterintuitive aspects of SQL. Many developers expect NOT to 'rescue' NULL values—making NULL rows appear in NOT queries. This is incorrect.

Key Principle: NOT UNKNOWN = UNKNOWN, and UNKNOWN rows are excluded from results.

not_with_null.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
-- Table: employees
-- | emp_id | name    | bonus |
-- |--------|---------|-------|
-- | 1      | Alice   | 5000  |
-- | 2      | Bob     | 0     |
-- | 3      | Charlie | NULL  |  -- Bonus unknown or not applicable
 
-- Query 1: Find employees with bonus > 1000
SELECT * FROM employees WHERE bonus > 1000;
-- Result: Alice only (Bob's 0 is not > 1000, Charlie's NULL is UNKNOWN)
 
-- Query 2: Find employees where bonus is NOT > 1000
SELECT * FROM employees WHERE NOT bonus > 1000;
-- Expected by many: Bob AND Charlie
-- Actual result: Bob only!
 
-- Why? For Charlie:
-- bonus > 1000 → NULL > 1000 → UNKNOWN
-- NOT UNKNOWN → UNKNOWN
-- UNKNOWN is not TRUE, so row is excluded
 
-- This means Query 1 and Query 2 don't cover all rows!
-- There's a "forgotten middle" where NULL lives.
 
-- To include Charlie, explicitly check for NULL:
SELECT * FROM employees 
WHERE NOT bonus > 1000 OR bonus IS NULL;
-- Result: Bob and Charlie
 
-- Or use COALESCE to convert NULL to a value:
SELECT * FROM employees
WHERE NOT COALESCE(bonus, 0) > 1000;
-- Result: Bob and Charlie (NULL treated as 0)

The Law of Excluded Middle Doesn't Hold:

In classical logic, for any proposition P: P OR NOT P is always TRUE (Law of Excluded Middle). In SQL's three-valued logic, this law fails for NULL:

NULL > 1000 → UNKNOWN
NOT (NULL > 1000) → UNKNOWN
UNKNOWN OR UNKNOWN → UNKNOWN (not TRUE!)

This means WHERE condition OR NOT condition does NOT return all rows if the condition involves NULL values.

Critical NULL Gotcha

If a query with condition C returns N rows, and a query with NOT C returns M rows, you might expect N + M = total rows. But with NULL values, N + M < total rows! The rows with NULL in relevant columns appear in neither result. Always consider: 'Where do my NULL rows go?'

NOT with Various NULL-Involving Expressions
Expression	Result	NOT Expression	Result
`NULL = 5`	UNKNOWN	`NOT NULL = 5`	UNKNOWN
`NULL > 10`	UNKNOWN	`NOT NULL > 10`	UNKNOWN
`NULL = NULL`	UNKNOWN	`NOT NULL = NULL`	UNKNOWN
`NULL IS NULL`	TRUE	`NOT NULL IS NULL`	FALSE
`5 IS NULL`	FALSE	`NOT 5 IS NULL`	TRUE

NOT with IN, BETWEEN, LIKE

NOT combines with several SQL operators to create powerful exclusionary conditions:

NOT IN excludes rows matching any value in a list:

not_in.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
-- Exclude specific categories
SELECT product_id, name, category
FROM products
WHERE category NOT IN ('Electronics', 'Clothing', 'Books');
 
-- Equivalent using AND/OR
SELECT product_id, name, category
FROM products
WHERE category != 'Electronics'
  AND category != 'Clothing'
  AND category != 'Books';
 
-- NOT IN with subquery
SELECT customer_id, name
FROM customers
WHERE customer_id NOT IN (
    SELECT DISTINCT customer_id FROM orders
);
-- Finds customers who have NEVER placed an order
 
-- ⚠️ CRITICAL NULL TRAP with NOT IN and subqueries:
-- If the subquery returns ANY NULL value, NOT IN returns no rows!
 
-- Dangerous: If orders.customer_id can be NULL:
SELECT * FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);
-- Returns EMPTY if any order.customer_id is NULL!
 
-- Safe: Exclude NULLs explicitly
SELECT * FROM customers
WHERE customer_id NOT IN (
    SELECT customer_id FROM orders 
    WHERE customer_id IS NOT NULL
);
 
-- Or use NOT EXISTS (safer with NULLs)
SELECT * FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

NOT IN Subquery NULL Trap

If a NOT IN subquery can return NULL, the entire NOT IN may return no rows. This is because x NOT IN (a, b, NULL) becomes x != a AND x != b AND x != NULL, and anything AND UNKNOWN = FALSE or UNKNOWN. Always filter NULLs from NOT IN subqueries or use NOT EXISTS instead.

De Morgan's Laws

When negating compound expressions, De Morgan's Laws define how NOT distributes over AND and OR. These laws are essential for understanding and simplifying negated conditions:

De Morgan's Laws:

NOT (A AND B) = (NOT A) OR (NOT B)
NOT (A OR B) = (NOT A) AND (NOT B)

In Words:

The negation of a conjunction (AND) is the disjunction (OR) of the negations
The negation of a disjunction (OR) is the conjunction (AND) of the negations

de_morgans_laws.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
-- De Morgan's Law 1: NOT (A AND B) = (NOT A) OR (NOT B)
 
-- Original: NOT (department = 'Sales' AND salary > 50000)
SELECT * FROM employees
WHERE NOT (department = 'Sales' AND salary > 50000);
 
-- Equivalent using De Morgan's Law:
SELECT * FROM employees
WHERE department != 'Sales' OR salary <= 50000;
 
-- Matches: Non-Sales employees OR employees earning <= 50000
 
 
-- De Morgan's Law 2: NOT (A OR B) = (NOT A) AND (NOT B)
 
-- Original: NOT (status = 'Pending' OR status = 'Processing')
SELECT * FROM orders
WHERE NOT (status = 'Pending' OR status = 'Processing');
 
-- Equivalent using De Morgan's Law:
SELECT * FROM orders
WHERE status != 'Pending' AND status != 'Processing';
 
-- Which is also equivalent to:
SELECT * FROM orders
WHERE status NOT IN ('Pending', 'Processing');
 
 
-- Complex example: negating a multi-condition expression
-- NOT (A AND B OR C) requires careful application
 
-- First, recognize operator precedence: A AND B OR C = (A AND B) OR C
-- NOT ((A AND B) OR C)
-- = NOT (A AND B) AND NOT C  [De Morgan on OR]
-- = (NOT A OR NOT B) AND NOT C  [De Morgan on AND]
 
SELECT * FROM products
WHERE NOT (
    (category = 'Electronics' AND price > 100) 
    OR discontinued = TRUE
);
 
-- Equivalent:
SELECT * FROM products
WHERE (category != 'Electronics' OR price <= 100)
  AND discontinued != TRUE;

De Morgan's Laws Quick Reference
Original Expression	Equivalent Expression
`NOT (A AND B)`	`(NOT A) OR (NOT B)`
`NOT (A OR B)`	`(NOT A) AND (NOT B)`
`NOT (A AND B AND C)`	`(NOT A) OR (NOT B) OR (NOT C)`
`NOT (A OR B OR C)`	`(NOT A) AND (NOT B) AND (NOT C)`

Mnemonic: NOT Flips the Operator

When you push NOT inside parentheses: AND becomes OR, OR becomes AND, and each condition gets negated. 'Break the lock, change the sign, negate each term.'

Practical Applications

NOT enables powerful exclusionary patterns in real-world queries:

practical_not.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
-- 1. EXCLUSION LISTS (blocklists, blacklists)
SELECT user_id, email
FROM users
WHERE email NOT LIKE '%@blocked-domain.com'
  AND user_id NOT IN (SELECT user_id FROM banned_users);
 
-- 2. DATA QUALITY (find incomplete records)
SELECT customer_id, name, email, phone
FROM customers
WHERE email IS NULL 
   OR phone IS NULL
   OR NOT email LIKE '%@%.%';  -- Invalid email format
 
-- 3. ANTI-PATTERNS (find violations)
SELECT order_id, order_date, ship_date
FROM orders
WHERE ship_date IS NOT NULL
  AND NOT ship_date >= order_date;  -- Shipped before ordered!
 
-- 4. ACTIVE RECORDS (exclude archived/deleted)
SELECT product_id, name
FROM products
WHERE NOT deleted
  AND NOT archived
  AND NOT discontinued;
 
-- Can also write as (more common):
SELECT product_id, name
FROM products
WHERE deleted = FALSE
  AND archived = FALSE
  AND discontinued = FALSE;
 
-- 5. ORPHAN DETECTION (records with no parent)
SELECT c.category_id, c.name
FROM categories c
WHERE NOT EXISTS (
    SELECT 1 FROM categories parent
    WHERE parent.category_id = c.parent_id
)
AND c.parent_id IS NOT NULL;  -- Has a parent_id but parent doesn't exist
 
-- 6. COVERAGE GAPS (what's not covered)
SELECT region_id, region_name
FROM regions r
WHERE NOT EXISTS (
    SELECT 1 FROM sales_reps sr
    WHERE sr.region_id = r.region_id
    AND sr.is_active = TRUE
);
 
-- 7. TEMPORAL GAPS (records outside time window)
SELECT event_id, event_name, event_date
FROM events
WHERE event_date NOT BETWEEN '2024-01-01' AND '2024-12-31';

Common Mistakes with NOT

Common Errors

•Expecting NOT to include NULLs — WHERE NOT column = 5 excludes NULL rows
•NOT IN with NULL subqueries — Returns no rows if subquery has any NULL
•Incorrect De Morgan application — Forgetting to flip AND↔OR
•Double negation confusion — NOT NOT x = x, but reduces clarity
•NOT != instead of = — WHERE NOT column != 5 is confusing; use column = 5

Best Practices

•Handle NULLs explicitly — Add OR column IS NULL when needed
•Use NOT EXISTS over NOT IN — Safer with potential NULL values
•Simplify where possible — Convert NOT column = 5 to column != 5
•Avoid double negation — Refactor NOT NOT x to x
•Apply De Morgan carefully — Verify transformations with truth tables

not_mistakes.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- MISTAKE 1: Expecting NOT to include NULLs
SELECT * FROM employees WHERE NOT bonus > 1000;
-- Does NOT include employees with NULL bonus!
-- Fix:
SELECT * FROM employees WHERE NOT bonus > 1000 OR bonus IS NULL;
 
-- MISTAKE 2: NOT IN with potentially NULL subquery
SELECT * FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);
-- Returns NOTHING if any order.customer_id is NULL!
-- Fix: Use NOT EXISTS or filter NULLs
SELECT * FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);
 
-- MISTAKE 3: Misapplying De Morgan's Law
-- Want: NOT (A AND B)
-- WRONG: NOT A AND NOT B (this is NOT (A OR B))
-- CORRECT: NOT A OR NOT B
 
-- MISTAKE 4: Confusing double negation
SELECT * FROM products WHERE NOT price != 100;
-- This is just: WHERE price = 100
-- Write the simpler form!
 
-- MISTAKE 5: NOT with OR when AND is needed
-- "Find products not Electronics and not Clothing" (means: exclude both)
-- WRONG (returns almost everything):
SELECT * FROM products WHERE NOT category = 'Electronics' OR NOT category = 'Clothing';
-- CORRECT:
SELECT * FROM products WHERE NOT category = 'Electronics' AND NOT category = 'Clothing';
-- Or:
SELECT * FROM products WHERE category NOT IN ('Electronics', 'Clothing');

Summary: Mastering NOT

The NOT operator provides essential negation capability in SQL. Let's consolidate the key insights:

Key Takeaways

•NOT performs logical negation — Inverts TRUE to FALSE and FALSE to TRUE. It's a unary operator with highest precedence.
•NOT UNKNOWN = UNKNOWN — Negating an unknown value remains unknown. NULL rows are excluded from NOT conditions just as from positive conditions.
•NOT IN NULL trap — If a NOT IN subquery returns any NULL, the entire result may be empty. Use NOT EXISTS for safety.
•De Morgan's Laws — NOT(A AND B) = NOT A OR NOT B; NOT(A OR B) = NOT A AND NOT B. Essential for understanding negation of compound expressions.
•NOT works with IN, BETWEEN, LIKE, EXISTS — These combinations enable powerful exclusionary patterns.
•Prefer simpler expressions — Use != over NOT =, and avoid double negation for clarity.

What's Next:

Now that we understand AND, OR, and NOT individually, the critical question becomes: how do they interact when combined? The next page explores operator precedence—the rules that determine evaluation order when multiple logical operators appear in a single expression.

Page Complete

You now understand the NOT operator comprehensively—from its simple truth table through the nuanced NULL behavior to De Morgan's Laws for compound expressions. Next, we'll explore how AND, OR, and NOT interact through operator precedence rules.

3 / 5

Loading learning content...

Database Management SystemsSQL Queries

Logical Operators

LevelBeginner

Duration55 mins

TopicSQL Queries

3 / 5

NOT Operator

The Power of Negation

Mastering NOT enables you to express exclusionary logic clearly and understand how negation propagates through complex expressions.

What You Will Learn

The NOT Operator Fundamentals

Formal Definition:

NOT condition → TRUE if condition is FALSE
NOT condition → FALSE if condition is TRUE

Key Characteristics:

Unary operator: Takes one operand (unlike binary AND/OR)
Prefix placement: Appears before the condition it negates
Highest precedence: Evaluated before AND and OR
Self-inverse: NOT (NOT condition) = condition (double negation)

basic_not_syntax.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- Basic NOT Syntax
SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;
 
-- Example: Find products that are NOT in Electronics category
SELECT product_id, name, category
FROM products
WHERE NOT category = 'Electronics';
 
-- Equivalent using !=
SELECT product_id, name, category
FROM products
WHERE category != 'Electronics';
 
-- NOT with comparison operators
SELECT order_id, status
FROM orders
WHERE NOT status = 'Completed';  -- Same as status != 'Completed'
 
-- NOT inverting a compound expression
SELECT employee_id, name
FROM employees
WHERE NOT (department = 'Sales' AND salary > 50000);
 
-- Double negation (equals original)
SELECT * FROM products
WHERE NOT NOT (price > 100);  -- Same as: WHERE price > 100

NOT vs Comparison Operators

Truth Table Analysis

NOT has the simplest truth table of the logical operators—it simply inverts its operand:

NOT Truth Table (Two-Valued Logic)
Condition	NOT Condition
TRUE	FALSE
FALSE	TRUE

Three-Valued Logic Extension:

In SQL's three-valued logic with NULL, the NOT truth table extends to include UNKNOWN:

NOT Truth Table with Three-Valued Logic
Condition	NOT Condition	Explanation
TRUE	FALSE	Inversion of TRUE
FALSE	TRUE	Inversion of FALSE
UNKNOWN	UNKNOWN	Cannot invert unknown value

NOT UNKNOWN = UNKNOWN

NOT and NULL Values

The behavior of NOT with NULL is one of the most counterintuitive aspects of SQL. Many developers expect NOT to 'rescue' NULL values—making NULL rows appear in NOT queries. This is incorrect.

Key Principle: NOT UNKNOWN = UNKNOWN, and UNKNOWN rows are excluded from results.

not_with_null.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
-- Table: employees
-- | emp_id | name    | bonus |
-- |--------|---------|-------|
-- | 1      | Alice   | 5000  |
-- | 2      | Bob     | 0     |
-- | 3      | Charlie | NULL  |  -- Bonus unknown or not applicable
 
-- Query 1: Find employees with bonus > 1000
SELECT * FROM employees WHERE bonus > 1000;
-- Result: Alice only (Bob's 0 is not > 1000, Charlie's NULL is UNKNOWN)
 
-- Query 2: Find employees where bonus is NOT > 1000
SELECT * FROM employees WHERE NOT bonus > 1000;
-- Expected by many: Bob AND Charlie
-- Actual result: Bob only!
 
-- Why? For Charlie:
-- bonus > 1000 → NULL > 1000 → UNKNOWN
-- NOT UNKNOWN → UNKNOWN
-- UNKNOWN is not TRUE, so row is excluded
 
-- This means Query 1 and Query 2 don't cover all rows!
-- There's a "forgotten middle" where NULL lives.
 
-- To include Charlie, explicitly check for NULL:
SELECT * FROM employees 
WHERE NOT bonus > 1000 OR bonus IS NULL;
-- Result: Bob and Charlie
 
-- Or use COALESCE to convert NULL to a value:
SELECT * FROM employees
WHERE NOT COALESCE(bonus, 0) > 1000;
-- Result: Bob and Charlie (NULL treated as 0)

The Law of Excluded Middle Doesn't Hold:

In classical logic, for any proposition P: P OR NOT P is always TRUE (Law of Excluded Middle). In SQL's three-valued logic, this law fails for NULL:

NULL > 1000 → UNKNOWN
NOT (NULL > 1000) → UNKNOWN
UNKNOWN OR UNKNOWN → UNKNOWN (not TRUE!)

This means WHERE condition OR NOT condition does NOT return all rows if the condition involves NULL values.

Critical NULL Gotcha

NOT with Various NULL-Involving Expressions
Expression	Result	NOT Expression	Result
`NULL = 5`	UNKNOWN	`NOT NULL = 5`	UNKNOWN
`NULL > 10`	UNKNOWN	`NOT NULL > 10`	UNKNOWN
`NULL = NULL`	UNKNOWN	`NOT NULL = NULL`	UNKNOWN
`NULL IS NULL`	TRUE	`NOT NULL IS NULL`	FALSE
`5 IS NULL`	FALSE	`NOT 5 IS NULL`	TRUE

NOT with IN, BETWEEN, LIKE

NOT combines with several SQL operators to create powerful exclusionary conditions:

NOT IN excludes rows matching any value in a list:

not_in.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
-- Exclude specific categories
SELECT product_id, name, category
FROM products
WHERE category NOT IN ('Electronics', 'Clothing', 'Books');
 
-- Equivalent using AND/OR
SELECT product_id, name, category
FROM products
WHERE category != 'Electronics'
  AND category != 'Clothing'
  AND category != 'Books';
 
-- NOT IN with subquery
SELECT customer_id, name
FROM customers
WHERE customer_id NOT IN (
    SELECT DISTINCT customer_id FROM orders
);
-- Finds customers who have NEVER placed an order
 
-- ⚠️ CRITICAL NULL TRAP with NOT IN and subqueries:
-- If the subquery returns ANY NULL value, NOT IN returns no rows!
 
-- Dangerous: If orders.customer_id can be NULL:
SELECT * FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);
-- Returns EMPTY if any order.customer_id is NULL!
 
-- Safe: Exclude NULLs explicitly
SELECT * FROM customers
WHERE customer_id NOT IN (
    SELECT customer_id FROM orders 
    WHERE customer_id IS NOT NULL
);
 
-- Or use NOT EXISTS (safer with NULLs)
SELECT * FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

NOT IN Subquery NULL Trap

De Morgan's Laws

When negating compound expressions, De Morgan's Laws define how NOT distributes over AND and OR. These laws are essential for understanding and simplifying negated conditions:

De Morgan's Laws:

NOT (A AND B) = (NOT A) OR (NOT B)
NOT (A OR B) = (NOT A) AND (NOT B)

In Words:

The negation of a conjunction (AND) is the disjunction (OR) of the negations
The negation of a disjunction (OR) is the conjunction (AND) of the negations

de_morgans_laws.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
-- De Morgan's Law 1: NOT (A AND B) = (NOT A) OR (NOT B)
 
-- Original: NOT (department = 'Sales' AND salary > 50000)
SELECT * FROM employees
WHERE NOT (department = 'Sales' AND salary > 50000);
 
-- Equivalent using De Morgan's Law:
SELECT * FROM employees
WHERE department != 'Sales' OR salary <= 50000;
 
-- Matches: Non-Sales employees OR employees earning <= 50000
 
 
-- De Morgan's Law 2: NOT (A OR B) = (NOT A) AND (NOT B)
 
-- Original: NOT (status = 'Pending' OR status = 'Processing')
SELECT * FROM orders
WHERE NOT (status = 'Pending' OR status = 'Processing');
 
-- Equivalent using De Morgan's Law:
SELECT * FROM orders
WHERE status != 'Pending' AND status != 'Processing';
 
-- Which is also equivalent to:
SELECT * FROM orders
WHERE status NOT IN ('Pending', 'Processing');
 
 
-- Complex example: negating a multi-condition expression
-- NOT (A AND B OR C) requires careful application
 
-- First, recognize operator precedence: A AND B OR C = (A AND B) OR C
-- NOT ((A AND B) OR C)
-- = NOT (A AND B) AND NOT C  [De Morgan on OR]
-- = (NOT A OR NOT B) AND NOT C  [De Morgan on AND]
 
SELECT * FROM products
WHERE NOT (
    (category = 'Electronics' AND price > 100) 
    OR discontinued = TRUE
);
 
-- Equivalent:
SELECT * FROM products
WHERE (category != 'Electronics' OR price <= 100)
  AND discontinued != TRUE;

De Morgan's Laws Quick Reference
Original Expression	Equivalent Expression
`NOT (A AND B)`	`(NOT A) OR (NOT B)`
`NOT (A OR B)`	`(NOT A) AND (NOT B)`
`NOT (A AND B AND C)`	`(NOT A) OR (NOT B) OR (NOT C)`
`NOT (A OR B OR C)`	`(NOT A) AND (NOT B) AND (NOT C)`

Mnemonic: NOT Flips the Operator

When you push NOT inside parentheses: AND becomes OR, OR becomes AND, and each condition gets negated. 'Break the lock, change the sign, negate each term.'

Practical Applications

NOT enables powerful exclusionary patterns in real-world queries:

practical_not.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
-- 1. EXCLUSION LISTS (blocklists, blacklists)
SELECT user_id, email
FROM users
WHERE email NOT LIKE '%@blocked-domain.com'
  AND user_id NOT IN (SELECT user_id FROM banned_users);
 
-- 2. DATA QUALITY (find incomplete records)
SELECT customer_id, name, email, phone
FROM customers
WHERE email IS NULL 
   OR phone IS NULL
   OR NOT email LIKE '%@%.%';  -- Invalid email format
 
-- 3. ANTI-PATTERNS (find violations)
SELECT order_id, order_date, ship_date
FROM orders
WHERE ship_date IS NOT NULL
  AND NOT ship_date >= order_date;  -- Shipped before ordered!
 
-- 4. ACTIVE RECORDS (exclude archived/deleted)
SELECT product_id, name
FROM products
WHERE NOT deleted
  AND NOT archived
  AND NOT discontinued;
 
-- Can also write as (more common):
SELECT product_id, name
FROM products
WHERE deleted = FALSE
  AND archived = FALSE
  AND discontinued = FALSE;
 
-- 5. ORPHAN DETECTION (records with no parent)
SELECT c.category_id, c.name
FROM categories c
WHERE NOT EXISTS (
    SELECT 1 FROM categories parent
    WHERE parent.category_id = c.parent_id
)
AND c.parent_id IS NOT NULL;  -- Has a parent_id but parent doesn't exist
 
-- 6. COVERAGE GAPS (what's not covered)
SELECT region_id, region_name
FROM regions r
WHERE NOT EXISTS (
    SELECT 1 FROM sales_reps sr
    WHERE sr.region_id = r.region_id
    AND sr.is_active = TRUE
);
 
-- 7. TEMPORAL GAPS (records outside time window)
SELECT event_id, event_name, event_date
FROM events
WHERE event_date NOT BETWEEN '2024-01-01' AND '2024-12-31';

Common Mistakes with NOT

Common Errors

•Expecting NOT to include NULLs — WHERE NOT column = 5 excludes NULL rows
•NOT IN with NULL subqueries — Returns no rows if subquery has any NULL
•Incorrect De Morgan application — Forgetting to flip AND↔OR
•Double negation confusion — NOT NOT x = x, but reduces clarity
•NOT != instead of = — WHERE NOT column != 5 is confusing; use column = 5

Best Practices

•Handle NULLs explicitly — Add OR column IS NULL when needed
•Use NOT EXISTS over NOT IN — Safer with potential NULL values
•Simplify where possible — Convert NOT column = 5 to column != 5
•Avoid double negation — Refactor NOT NOT x to x
•Apply De Morgan carefully — Verify transformations with truth tables

not_mistakes.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- MISTAKE 1: Expecting NOT to include NULLs
SELECT * FROM employees WHERE NOT bonus > 1000;
-- Does NOT include employees with NULL bonus!
-- Fix:
SELECT * FROM employees WHERE NOT bonus > 1000 OR bonus IS NULL;
 
-- MISTAKE 2: NOT IN with potentially NULL subquery
SELECT * FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);
-- Returns NOTHING if any order.customer_id is NULL!
-- Fix: Use NOT EXISTS or filter NULLs
SELECT * FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);
 
-- MISTAKE 3: Misapplying De Morgan's Law
-- Want: NOT (A AND B)
-- WRONG: NOT A AND NOT B (this is NOT (A OR B))
-- CORRECT: NOT A OR NOT B
 
-- MISTAKE 4: Confusing double negation
SELECT * FROM products WHERE NOT price != 100;
-- This is just: WHERE price = 100
-- Write the simpler form!
 
-- MISTAKE 5: NOT with OR when AND is needed
-- "Find products not Electronics and not Clothing" (means: exclude both)
-- WRONG (returns almost everything):
SELECT * FROM products WHERE NOT category = 'Electronics' OR NOT category = 'Clothing';
-- CORRECT:
SELECT * FROM products WHERE NOT category = 'Electronics' AND NOT category = 'Clothing';
-- Or:
SELECT * FROM products WHERE category NOT IN ('Electronics', 'Clothing');

Summary: Mastering NOT

The NOT operator provides essential negation capability in SQL. Let's consolidate the key insights:

Key Takeaways

•NOT performs logical negation — Inverts TRUE to FALSE and FALSE to TRUE. It's a unary operator with highest precedence.
•NOT UNKNOWN = UNKNOWN — Negating an unknown value remains unknown. NULL rows are excluded from NOT conditions just as from positive conditions.
•NOT IN NULL trap — If a NOT IN subquery returns any NULL, the entire result may be empty. Use NOT EXISTS for safety.
•De Morgan's Laws — NOT(A AND B) = NOT A OR NOT B; NOT(A OR B) = NOT A AND NOT B. Essential for understanding negation of compound expressions.
•NOT works with IN, BETWEEN, LIKE, EXISTS — These combinations enable powerful exclusionary patterns.
•Prefer simpler expressions — Use != over NOT =, and avoid double negation for clarity.

What's Next:

Page Complete

3 / 5