SQL Logical Operators: AND, OR, NOT

Logical operators combine multiple conditions in WHERE, HAVING, and CASE WHEN clauses. Getting their behavior right — especially operator precedence — is one of the most common sources of subtle query bugs.

AND

AND requires ALL conditions to be true. A row is returned only if every AND condition evaluates to TRUE:

SELECT name, price, category
FROM products
WHERE category = 'electronics'
  AND price < 300
  AND is_active = TRUE;

If any condition is FALSE or NULL, AND returns FALSE (or NULL) — the row is excluded.

OR

OR requires AT LEAST ONE condition to be true. A row is returned if any OR condition is TRUE:

SELECT name, city
FROM customers
WHERE city = 'London'
   OR city = 'Manchester'
   OR city = 'Birmingham';

-- Cleaner alternative with IN
WHERE city IN ('London', 'Manchester', 'Birmingham')

If one condition is TRUE, OR returns TRUE even if others are NULL or FALSE.

NOT

NOT negates a condition — it turns TRUE into FALSE and FALSE into TRUE:

WHERE NOT is_deleted
WHERE NOT status = 'cancelled'   -- same as: WHERE status != 'cancelled'
WHERE NOT price BETWEEN 50 AND 200
WHERE NOT city IN ('London', 'Paris')  -- same as: WHERE city NOT IN (...)
WHERE NOT email LIKE '%@test.com'
WHERE NOT EXISTS (...)

Operator Precedence

This is where bugs hide. SQL evaluates logical operators in this order:

NOT (highest)
AND
OR (lowest)

-- What does this mean?
WHERE category = 'books' OR category = 'music' AND price > 20

-- SQL evaluates AND first:
WHERE category = 'books' OR (category = 'music' AND price > 20)

-- This returns:
--   ALL books (any price)
--   Only music priced over $20

-- If you wanted (books OR music) with price > 20:
WHERE (category = 'books' OR category = 'music') AND price > 20

Always add parentheses when mixing AND and OR. Even if you understand the precedence, parentheses make the intent clear to the next reader.

Truth Tables

How logical operators handle TRUE, FALSE, and NULL:

AND:

TRUE  AND TRUE  = TRUE
TRUE  AND FALSE = FALSE
TRUE  AND NULL  = NULL   (unknown — row excluded)
FALSE AND TRUE  = FALSE
FALSE AND FALSE = FALSE
FALSE AND NULL  = FALSE  (one FALSE makes AND false regardless)
NULL  AND NULL  = NULL

OR:

TRUE  OR TRUE  = TRUE
TRUE  OR FALSE = TRUE
TRUE  OR NULL  = TRUE   (one TRUE makes OR true regardless)
FALSE OR TRUE  = TRUE
FALSE OR FALSE = FALSE
FALSE OR NULL  = NULL   (unknown — row excluded)
NULL  OR NULL  = NULL

NOT:

NOT TRUE  = FALSE
NOT FALSE = TRUE
NOT NULL  = NULL   (NOT of unknown is still unknown)

The NULL behavior is the surprising part. NOT NULL evaluates to NULL — not TRUE. This is why WHERE phone != NULL never returns rows: it’s checking NOT (phone = NULL) which evaluates to NOT NULL = NULL.

Combining All Three

-- Customers who:
-- ARE in London or Manchester
-- AND are active
-- AND do NOT have a test email
SELECT name, email, city
FROM customers
WHERE (city = 'London' OR city = 'Manchester')
  AND is_active = TRUE
  AND NOT email LIKE '%@test.com'
ORDER BY city, name;

De Morgan’s Laws in SQL

De Morgan’s laws are useful when simplifying negated conditions:

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

-- Original
WHERE NOT (status = 'cancelled' OR status = 'refunded')

-- Equivalent (De Morgan applied)
WHERE status != 'cancelled' AND status != 'refunded'

-- Even cleaner
WHERE status NOT IN ('cancelled', 'refunded')

Practical Examples

Multi-condition filter:

SELECT product_id, name, price, stock_quantity
FROM products
WHERE is_active = TRUE
  AND price BETWEEN 50 AND 500
  AND stock_quantity > 0
  AND (category = 'electronics' OR category = 'accessories')
ORDER BY price;

Exclude specific statuses with date filter:

SELECT order_id, customer_id, amount
FROM orders
WHERE status NOT IN ('cancelled', 'refunded', 'test')
  AND amount > 0
  AND created_at >= '2025-01-01'
ORDER BY created_at DESC;

Flexible search — match any of several conditions:

SELECT * FROM products
WHERE
    name ILIKE '%keyboard%'
    OR description ILIKE '%keyboard%'
    OR tags @> ARRAY['keyboard']
ORDER BY name;

Find records that fall outside normal ranges (anomaly detection):

SELECT order_id, amount, customer_id
FROM orders
WHERE NOT (amount BETWEEN 1 AND 10000)
   OR amount IS NULL
ORDER BY amount DESC;