SQL 17 🛢️ Logical Operators: AND, OR, and NOT - KandZ

Comparison operators test a single condition. Logical operators combine multiple conditions. The AND operator requires both conditions to be true. The OR operator requires at least one condition to be true. The NOT operator negates a condition. Together, they form the boolean algebra that every WHERE clause, every CASE expression, and every CHECK constraint is built on.

The previous chapter covered the comparison operators. This chapter covers the logical operators that combine them. The logical operators are simple in concept but subtle in behavior because SQL uses three-valued logic. A condition that involves NULL does not return TRUE or FALSE. It returns NULL. The logical operators must handle this third value, and the rules are not the same as in most programming languages.

Key point: SQL’s logical operators follow three-valued logic (also called ternary logic). Every expression evaluates to TRUE, FALSE, or NULL. The WHERE clause includes only the rows where the condition is TRUE. The AND, OR, and NOT operators propagate NULL according to a specific truth table. Understanding that table is the difference between a query that filters correctly and one that silently excludes rows.

Why logical operators matter

A single comparison is rarely enough. The real questions are compound: “Which orders are over $100 and were placed last month?” “Which customers are in New York or Chicago?” “Which users are not active?” The logical operators combine comparisons into compound conditions.

The combination problem. The AND operator narrows the result set. Both conditions must be true. The OR operator widens the result set. At least one condition must be true. The NOT operator inverts a condition. It returns TRUE when the condition is FALSE.

The precedence problem. The AND operator has higher precedence than the OR operator. Without parentheses, the AND is evaluated first. A query like WHERE city = 'NY' OR city = 'CHI' AND active = TRUE is evaluated as WHERE city = 'NY' OR (city = 'CHI' AND active = TRUE). The parentheses are not required by the syntax, but they are required for clarity.

The NULL problem. A condition that involves NULL returns NULL. The AND operator returns FALSE if any operand is FALSE, even if another operand is NULL. The OR operator returns TRUE if any operand is TRUE, even if another operand is NULL. The NOT operator returns NULL if the operand is NULL. These rules are different from two-valued logic.

The short-circuit problem. The AND operator short-circuits. If the first condition is FALSE, the second condition is not evaluated. The OR operator short-circuits. If the first condition is TRUE, the second condition is not evaluated. The short-circuit behavior matters when one of the conditions is expensive or has a side effect.

The trade-off. Three-valued logic is more complex than two-valued logic. It catches bugs that two-valued logic would miss, but it also introduces subtle behaviors that developers must learn. A query that works in a programming language’s if statement may not work in SQL’s WHERE clause.

a. The AND Operator

The AND operator requires both conditions to be true. It returns TRUE only when both operands are TRUE.

SELECT * FROM orders WHERE total > 100 AND order_date >= '2026-01-01';

The query selects orders with a total greater than 100 that were placed on or after January 1, 2026. Both conditions must be true for a row to be included.

The truth table for AND is:

A	B	A AND B
TRUE	TRUE	TRUE
TRUE	FALSE	FALSE
TRUE	NULL	NULL
FALSE	TRUE	FALSE
FALSE	FALSE	FALSE
FALSE	NULL	FALSE
NULL	TRUE	NULL
NULL	FALSE	FALSE
NULL	NULL	NULL

The key rows are the ones with NULL. When one operand is FALSE, the result is FALSE regardless of the other operand. When one operand is NULL and the other is TRUE, the result is NULL. When both operands are NULL, the result is NULL.

The AND operator can be chained. a AND b AND c is TRUE only when all three are TRUE.

SELECT * FROM orders
WHERE total > 100
  AND order_date >= '2026-01-01'
  AND status = 'shipped';

The query selects orders that satisfy all three conditions.

b. The OR Operator

The OR operator requires at least one condition to be true. It returns TRUE when either operand is TRUE.

SELECT * FROM customers WHERE city = 'New York' OR city = 'Chicago';

The query selects customers in New York or Chicago. Either condition can be true for a row to be included.

The truth table for OR is:

A	B	A OR B
TRUE	TRUE	TRUE
TRUE	FALSE	TRUE
TRUE	NULL	TRUE
FALSE	TRUE	TRUE
FALSE	FALSE	FALSE
FALSE	NULL	NULL
NULL	TRUE	TRUE
NULL	FALSE	NULL
NULL	NULL	NULL

The key rows are the ones with NULL. When one operand is TRUE, the result is TRUE regardless of the other operand. When one operand is NULL and the other is FALSE, the result is NULL. When both operands are NULL, the result is NULL.

The OR operator can be chained. a OR b OR c is TRUE when at least one is TRUE.

SELECT * FROM customers
WHERE city = 'New York'
   OR city = 'Chicago'
   OR city = 'Boston';

The query selects customers in any of the three cities. The IN operator is a cleaner equivalent.

SELECT * FROM customers WHERE city IN ('New York', 'Chicago', 'Boston');

c. The NOT Operator and Precedence

The NOT operator negates a condition. It returns TRUE when the condition is FALSE, FALSE when the condition is TRUE, and NULL when the condition is NULL.

SELECT * FROM users WHERE NOT is_active;
SELECT * FROM orders WHERE NOT (status = 'cancelled');

The first query selects users who are not active. The second selects orders that are not cancelled.

The truth table for NOT is:

A	NOT A
TRUE	FALSE
FALSE	TRUE
NULL	NULL

The NOT operator returns NULL when the operand is NULL. A row with a null is_active is excluded from WHERE NOT is_active.

The precedence of the logical operators is:

NOT (highest)
AND
OR (lowest)

The precedence determines the order of evaluation. NOT a AND b is evaluated as (NOT a) AND b. a OR b AND c is evaluated as a OR (b AND c).

Parentheses override the precedence. (a OR b) AND c is evaluated as the OR first, then the AND. The parentheses change the result.

-- Without parentheses, AND is evaluated first
SELECT * FROM customers
WHERE city = 'New York' OR city = 'Chicago' AND is_active = TRUE;

-- With parentheses, OR is evaluated first
SELECT * FROM customers
WHERE (city = 'New York' OR city = 'Chicago') AND is_active = TRUE;

The first query selects customers in New York, plus customers in Chicago who are active. The second query selects customers in either city who are active. The results are different.

The NOT operator is often combined with IN, BETWEEN, and LIKE.

SELECT * FROM customers WHERE city NOT IN ('New York', 'Chicago');
SELECT * FROM orders WHERE total NOT BETWEEN 50 AND 200;
SELECT * FROM customers WHERE first_name NOT LIKE 'A%';

The NOT IN, NOT BETWEEN, and NOT LIKE operators are the negations of their positive counterparts.

The NOT IN operator has a known trap. If the subquery or the list contains a NULL, the result is NULL for every row, and no rows are returned. The NOT EXISTS operator does not have this problem.

-- This returns no rows if the subquery returns any NULL
SELECT * FROM customers WHERE customer_id NOT IN (SELECT customer_id FROM orders);

-- This is the safe alternative
SELECT * FROM customers
WHERE NOT EXISTS (SELECT 1 FROM orders WHERE orders.customer_id = customers.customer_id);

The NOT IN trap is one of the most common sources of bugs in SQL queries. The NOT EXISTS operator is the safe alternative.

Complete Example Session

This session demonstrates the logical operators on a small table.

-- ============================================
-- PART 1: THE TABLE
-- ============================================

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    first_name  VARCHAR(50) NOT NULL,
    last_name   VARCHAR(50) NOT NULL,
    city        VARCHAR(50),
    is_active   BOOLEAN
);

INSERT INTO customers VALUES
    (1, 'Alice', 'Johnson', 'New York', TRUE),
    (2, 'Bob', 'Smith', 'Chicago', TRUE),
    (3, 'Carol', 'Williams', 'New York', FALSE),
    (4, 'Dave', 'Brown', NULL, TRUE),
    (5, 'Eve', 'Davis', 'Boston', NULL);

-- ============================================
-- PART 2: AND
-- ============================================

SELECT * FROM customers WHERE city = 'New York' AND is_active = TRUE;

-- Output: Alice
-- Both conditions must be true.

-- ============================================
-- PART 3: OR
-- ============================================

SELECT * FROM customers WHERE city = 'New York' OR city = 'Chicago';

-- Output: Alice, Bob, Carol

-- ============================================
-- PART 4: NOT
-- ============================================

SELECT * FROM customers WHERE NOT is_active;

-- Output: Carol
-- Eve is excluded because is_active is NULL.

-- ============================================
-- PART 5: THE PRECEDENCE TRAP
-- ============================================

SELECT * FROM customers
WHERE city = 'New York' OR city = 'Chicago' AND is_active = TRUE;

-- Output: Alice, Bob, Carol
-- Evaluated as: city = 'New York' OR (city = 'Chicago' AND is_active = TRUE)

SELECT * FROM customers
WHERE (city = 'New York' OR city = 'Chicago') AND is_active = TRUE;

-- Output: Alice, Bob
-- The parentheses change the result.

-- ============================================
-- PART 6: THE NULL BEHAVIOR
-- ============================================

SELECT * FROM customers WHERE city IS NULL AND is_active = TRUE;

-- Output: Dave
-- Both conditions are true.

SELECT * FROM customers WHERE city = 'Boston' OR is_active IS NULL;

-- Output: Dave (via is_active IS NULL), Eve (via city = 'Boston')
-- The OR includes rows where either condition is true.

-- ============================================
-- PART 7: NOT IN
-- ============================================

SELECT * FROM customers WHERE city NOT IN ('New York', 'Chicago');

-- Output: Eve
-- Dave is excluded because city is NULL.

-- ============================================
-- PART 8: THE NOT IN TRAP
-- ============================================

-- The NOT IN trap happens with a subquery that returns NULL.
-- If the subquery returns any NULL, the NOT IN returns no rows.

-- This is the safe alternative:
SELECT * FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

-- ============================================
-- PART 9: THE SHORT-CIRCUIT
-- ============================================

-- The AND operator short-circuits.
-- If the first condition is FALSE, the second is not evaluated.

-- The OR operator short-circuits.
-- If the first condition is TRUE, the second is not evaluated.

-- The short-circuit behavior is not guaranteed by the SQL standard
-- but is implemented by most databases.

-- ============================================
-- PART 10: THE SUMMARY
-- ============================================

-- AND : both conditions true
-- OR  : at least one condition true
-- NOT : negates the condition
-- Precedence: NOT > AND > OR
-- Parentheses override precedence
-- NULL propagates through the operators

The ten parts cover the table, AND, OR, NOT, the precedence trap, the null behavior, NOT IN, the NOT IN trap, the short-circuit, and the summary.

Quick Reference

The Logical Operators

Operator	Purpose
`AND`	Both conditions true
`OR`	At least one condition true
`NOT`	Negates a condition

The Precedence

Precedence	Operator
1 (highest)	`NOT`
2	`AND`
3 (lowest)	`OR`

The AND Truth Table

A	B	A AND B
T	T	T
T	F	F
T	NULL	NULL
F	T	F
F	F	F
F	NULL	F
NULL	T	NULL
NULL	F	F
NULL	NULL	NULL

The OR Truth Table

A	B	A OR B
T	T	T
T	F	T
T	NULL	T
F	T	T
F	F	F
F	NULL	NULL
NULL	T	T
NULL	F	NULL
NULL	NULL	NULL

The NOT Truth Table

A	NOT A
T	F
F	T
NULL	NULL

Best Practices

✅ Do This:

-- Use parentheses to clarify precedence
WHERE (city = 'New York' OR city = 'Chicago') AND is_active = TRUE -- ✅

-- Use NOT EXISTS instead of NOT IN with a subquery
WHERE NOT EXISTS (SELECT 1 FROM orders WHERE ...)               -- ✅

-- Use IN instead of chained OR
WHERE city IN ('New York', 'Chicago', 'Boston')                  -- ✅

-- Test the truth table for null behavior
WHERE city IS NULL OR is_active IS NULL                          -- ✅

❌ Don’t Do This:

-- Don't rely on the AND/OR precedence
WHERE city = 'NY' OR city = 'CHI' AND active = TRUE             -- ⚠️

-- Don't use NOT IN with a subquery that may return null
WHERE id NOT IN (SELECT id FROM t)                              -- ❌

-- Don't forget the NULL propagation
WHERE NOT is_active  -- excludes null rows                      -- ⚠️

-- Don't chain more than a few conditions without parentheses
WHERE a OR b AND c OR d AND e                                    -- ❌

Common Pitfalls

Pitfall	Why It Happens	Fix
Wrong results	Precedence misread	Add parentheses
`NOT IN` returns no rows	Subquery returns null	Use `NOT EXISTS`
`NOT` excludes null rows	Null propagation	Add `OR col IS NULL`
`AND`/`OR` unexpected	Short-circuit not guaranteed	Don’t rely on side effects

Real-World Examples

1. AND

SELECT * FROM orders WHERE total > 100 AND status = 'shipped';

2. OR

SELECT * FROM customers WHERE city = 'New York' OR city = 'Chicago';

3. NOT

SELECT * FROM users WHERE NOT is_active;

4. Parentheses

SELECT * FROM customers WHERE (city = 'New York' OR city = 'Chicago') AND is_active = TRUE;

5. NOT IN

SELECT * FROM customers WHERE city NOT IN ('New York', 'Chicago');

6. NOT BETWEEN

SELECT * FROM orders WHERE total NOT BETWEEN 50 AND 200;

7. NOT LIKE

SELECT * FROM customers WHERE first_name NOT LIKE 'A%';

8. NOT EXISTS

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

9. Null-Safe OR

SELECT * FROM customers WHERE city = 'Boston' OR is_active IS NULL;

10. Combined

SELECT * FROM orders WHERE (total > 100 OR total IS NULL) AND status <> 'cancelled';

Visual

The Logical Operators

┌──────────────────────────────────────────────┐
│  AND : both conditions true                  │
│  OR  : at least one condition true           │
│  NOT : negates the condition                 │
│                                              │
│  Precedence: NOT > AND > OR                  │
│  Parentheses override precedence             │
│                                              │
└──────────────────────────────────────────────┘

The Precedence

┌──────────────────────────────────────────────┐
│  NOT                                         │
│    │                                         │
│    ▼                                         │
│  AND                                         │
│    │                                         │
│    ▼                                         │
│  OR                                          │
│                                              │
│  a OR b AND NOT c                            │
│    └─ evaluated as a OR (b AND (NOT c))      │
│                                              │
└──────────────────────────────────────────────┘

The NULL Propagation

┌──────────────────────────────────────────────┐
│  NULL PROPAGATION                            │
│                                              │
│  TRUE AND NULL → NULL                        │
│  FALSE AND NULL → FALSE                      │
│                                              │
│  TRUE OR NULL → TRUE                         │
│  FALSE OR NULL → NULL                        │
│                                              │
│  NOT NULL → NULL                             │
│                                              │
│  WHERE includes only TRUE.                   │
│  FALSE and NULL are excluded.                │
│                                              │
└──────────────────────────────────────────────┘

The NOT IN Trap

┌──────────────────────────────────────────────┐
│  NOT IN TRAP                                 │
│                                              │
│  WHERE id NOT IN (SELECT id FROM t)          │
│    └─ If the subquery returns NULL,          │
│       the result is NULL for every row.      │
│    └─ No rows returned.                      │
│                                              │
│  Safe alternative:                           │
│  WHERE NOT EXISTS (SELECT 1 FROM t WHERE ...)│
│    └─ Handles null correctly.                │
│                                              │
└──────────────────────────────────────────────┘

Summary

Item	Value
`AND`	Both conditions true
`OR`	At least one condition true
`NOT`	Negates a condition
Precedence	`NOT` > `AND` > `OR`
Parentheses	Override precedence
`AND` with NULL	`FALSE` dominates, else `NULL`
`OR` with NULL	`TRUE` dominates, else `NULL`
`NOT` with NULL	Returns `NULL`
`NOT IN`	Trap with null subquery
`NOT EXISTS`	Safe alternative

Key takeaways:

The AND operator requires both conditions to be true. It returns TRUE only when both operands are TRUE. If one operand is FALSE, the result is FALSE. If one operand is NULL and the other is TRUE, the result is NULL .
The OR operator requires at least one condition to be true. It returns TRUE when either operand is TRUE. If one operand is NULL and the other is FALSE, the result is NULL. If both operands are NULL, the result is NULL .
The NOT operator negates a condition. It returns TRUE when the operand is FALSE, FALSE when the operand is TRUE, and NULL when the operand is NULL. A row with a null value is excluded from WHERE NOT condition .
The AND operator has higher precedence than the OR operator. The NOT operator has the highest precedence. Parentheses override the precedence and should be used for clarity when mixing AND and OR .
The NOT IN operator has a null trap. If the subquery or the list contains a NULL, the result is NULL for every row, and no rows are returned. The NOT EXISTS operator is the safe alternative .
The short-circuit behavior is not guaranteed by the SQL standard. Most databases short-circuit AND when the first condition is FALSE and OR when the first condition is TRUE. Do not rely on the short-circuit for side effects.
Three-valued logic is the rule. Every condition evaluates to TRUE, FALSE, or NULL. The WHERE clause includes only the rows where the condition is TRUE. Rows where the condition is FALSE or NULL are excluded.

Remember: The logical operators combine conditions. AND narrows. OR widens. NOT inverts. The precedence is NOT > AND > OR. Parentheses override. NULL propagates through the operators. The WHERE clause includes only TRUE. The NOT IN trap is real. Use NOT EXISTS instead. Three-valued logic is the rule.

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!