AND, OR, NOT Operators

Learning Focus

Use this lesson to understand AND, OR, NOT Operators with practical syntax and examples.

Concept Overview

Definition

AND, OR, and NOT are logical operators that combine or negate conditions. You most often use them in WHERE clauses, but they also appear in JOIN ... ON conditions and HAVING filters.

PostgreSQL evaluates boolean expressions using three-valued logic:

• true
• false
• NULL (unknown)

That third value is why boolean filtering is a frequent source of subtle bugs.

Why is it important?

• Correctness: missing parentheses can return the wrong rows without errors
• NULL awareness: NOT, AND, and OR behave differently when NULL is involved
• Performance: boolean logic affects selectivity and which indexes can be used

Where does it fit?

These operators are the foundation for filtering, and they connect directly to later topics like LIKE, IN, BETWEEN, CASE, and EXISTS.

---

Syntax & Rules

Core Syntax

SELECT select_list
FROM table_name
WHERE condition1 AND condition2;

SELECT select_list
FROM table_name
WHERE condition1 OR condition2;

SELECT select_list
FROM table_name
WHERE NOT condition1;

Available Options / Parameters

Operator	Meaning	Notes
AND	both conditions must be true	if one side is false, the result is false even if the other side is NULL
OR	at least one condition must be true	if one side is true, the result is true even if the other side is NULL
NOT	negates a condition	NOT NULL is NULL (unknown)

Precedence (Highest to Lowest)

Priority	Operator
1	NOT
2	AND
3	OR

Key Rules and Considerations

• Use parentheses whenever you mix AND and OR.
• If a boolean column is nullable, prefer flag IS TRUE / flag IS NOT TRUE over shorthand flag / NOT flag.
• Keep predicates sargable for performance (avoid wrapping indexed columns in functions).
• Use IN (...) when you have many equality alternatives on the same column.
• Debug complex filters by starting broad and adding one condition at a time.

---

Step-by-Step Examples

Example 1: AND to Narrow Results (Beginner)

-- Sample data
CREATE TABLE customers (
  customer_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  full_name   text NOT NULL,
  city        text NOT NULL,
  active      boolean,
  purchases   integer NOT NULL DEFAULT 0 CHECK (purchases >= 0)
);

INSERT INTO customers (full_name, city, active, purchases) VALUES
  ('Alice', 'New York', true, 5),
  ('Bob', 'Los Angeles', false, 3),
  ('Carol', 'Chicago', true, 10),
  ('Dave', 'New York', true, 2),
  ('Eve', 'Chicago', NULL, 7);

-- Active customers from Chicago
SELECT customer_id, full_name, city, purchases
FROM customers
WHERE city = 'Chicago' AND active IS TRUE
ORDER BY customer_id;

Expected output:

 customer_id | full_name | city    | purchases
-------------+-----------+---------+-----------
 3           | Carol     | Chicago | 10
(1 row)

---

Example 2: OR to Broaden Results (Intermediate)

Goal: customers who are in New York OR who have more than 8 purchases.

SELECT customer_id, full_name, city, purchases
FROM customers
WHERE city = 'New York' OR purchases > 8
ORDER BY customer_id;

Expected output:

 customer_id | full_name | city     | purchases
-------------+-----------+----------+-----------
 1           | Alice     | New York | 5
 3           | Carol     | Chicago  | 10
 4           | Dave      | New York | 2
(3 rows)

---

Example 3: Parentheses Control Meaning (Advanced)

When you mix OR and AND, add parentheses so the logic matches your intent.

Intent: active customers who are either in New York OR have more than 8 purchases.

-- Correct: group the OR conditions, then apply AND
SELECT customer_id, full_name, city, purchases, active
FROM customers
WHERE (city = 'New York' OR purchases > 8)
  AND active IS TRUE
ORDER BY customer_id;

Expected output:

 customer_id | full_name | city     | purchases | active
-------------+-----------+----------+-----------+--------
 1           | Alice     | New York | 5         | t
 3           | Carol     | Chicago  | 10        | t
 4           | Dave      | New York | 2         | t
(3 rows)

Now compare it to an ungrouped version that relies on precedence:

-- AND binds tighter than OR, so this is different logic:
-- city = 'New York' OR (purchases > 8 AND active IS TRUE)
SELECT customer_id, full_name, city, purchases, active
FROM customers
WHERE city = 'New York' OR purchases > 8 AND active IS TRUE
ORDER BY customer_id;

This query includes New York customers regardless of active (which may or may not be what you intended).

---

Example 4: NOT with Nullable Booleans

If active can be NULL, NOT active does not mean “not active”. It means “negate the value”, and NOT NULL is still NULL.

Use IS NOT TRUE when your business meaning is “false or unknown”.

-- Customers that are not active (false OR NULL)
SELECT customer_id, full_name, active
FROM customers
WHERE active IS NOT TRUE
ORDER BY customer_id;

Expected output:

 customer_id | full_name | active
-------------+-----------+--------
 2           | Bob       | f
 5           | Eve       |
(2 rows)

---

Practical Use Cases

1) Feature flags and rollouts

SELECT feature_key
FROM feature_flags
WHERE enabled IS TRUE
  AND (rollout_percent >= 100 OR user_bucket < rollout_percent);

2) Marketing segmentation

SELECT customer_id, full_name
FROM customers
WHERE (city = 'New York' OR city = 'Chicago')
  AND purchases >= 5
  AND active IS TRUE;

3) Queue selection for background jobs

SELECT job_id
FROM jobs
WHERE status = 'queued'
  AND attempts < 5
  AND (run_after IS NULL OR run_after <= now())
ORDER BY created_at
LIMIT 100;

4) Data quality checks

SELECT lead_id, email
FROM leads
WHERE email IS NULL OR email = '';

5) Security auditing

SELECT user_id, email
FROM users
WHERE active IS TRUE
  AND (mfa_enabled IS FALSE OR mfa_enabled IS NULL);

---

Common Mistakes & Troubleshooting

1) Missing parentheses when mixing AND and OR

Wrong SQL:

WHERE city = 'New York' OR purchases > 8 AND active IS TRUE

Bad outcome:

• The expression is interpreted as: city = 'New York' OR (purchases > 8 AND active IS TRUE).

Fix:

WHERE (city = 'New York' OR purchases > 8)
  AND active IS TRUE

---

2) Negating a group without parentheses

Wrong SQL:

WHERE NOT city = 'New York' OR city = 'Chicago'

Bad outcome:

• Readers often misinterpret what is negated.

Fix:

WHERE NOT (city = 'New York' OR city = 'Chicago')

---

3) Treating nullable booleans as if they were NOT NULL

Wrong SQL:

WHERE NOT active

Bad outcome:

• Rows where active IS NULL are not returned.

Fix:

WHERE active IS NOT TRUE

---

4) Broad OR predicates lead to slow scans

Wrong approach:

SELECT *
FROM orders
WHERE customer_id = 42 OR status = 'pending';

Bad outcome:

• The predicate is often not selective, and the planner may choose a scan.

Fix (when correctness allows splitting):

SELECT * FROM orders WHERE customer_id = 42
UNION ALL
SELECT * FROM orders WHERE status = 'pending' AND customer_id <> 42;

---

Debugging checklist:

1. Write the intended logic in plain language.
2. Add parentheses around every mixed AND/OR group.
3. Decide how NULL should behave (especially for nullable booleans).
4. Verify with a small preview SELECT and known edge-case rows.
5. Use EXPLAIN when a filter is unexpectedly slow.

---

Best Practices

• ✅ Add parentheses for readability (even if precedence would work). ❌ Avoid relying on precedence in complex expressions.
• ✅ Use IS TRUE / IS NOT TRUE for nullable boolean columns. ❌ Avoid shorthand flag / NOT flag unless flag is NOT NULL.
• ✅ Prefer IN (...) over many OR checks on the same column. ❌ Avoid long col = 'a' OR col = 'b' OR ... chains.
• ✅ Keep filters composable and test them incrementally. ❌ Avoid giant predicates that are difficult to review.
• ✅ Ensure your high-traffic filters are index-supported. ❌ Avoid non-sargable predicates that disable index usage.

---

Hands-On Practice

Use this setup for the exercises:

CREATE TABLE practice_employees (
  employee_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  department  text NOT NULL,
  active      boolean,
  experience_years integer NOT NULL CHECK (experience_years >= 0)
);

INSERT INTO practice_employees (department, active, experience_years)
VALUES
  ('Engineering', true, 5),
  ('Design', false, 2),
  ('Engineering', true, 8),
  ('HR', true, 3),
  ('Design', NULL, 6);

Exercise 1 (Easy): List active employees

Task: Select employees where active is true.

-- Your SQL here

Solution:

SELECT employee_id, department, active
FROM practice_employees
WHERE active IS TRUE
ORDER BY employee_id;

---

Exercise 2 (Medium): Combine AND with OR using parentheses

Task: Return employees that are active and either in Engineering or have more than 5 years of experience.

-- Your SQL here

Solution:

SELECT employee_id, department, active, experience_years
FROM practice_employees
WHERE active IS TRUE
  AND (department = 'Engineering' OR experience_years > 5)
ORDER BY employee_id;

---

Exercise 3 (Advanced): Handle nullable booleans explicitly

Task: Return employees that are “not active” defined as false OR NULL.

-- Your SQL here

Solution:

SELECT employee_id, department, active
FROM practice_employees
WHERE active IS NOT TRUE
ORDER BY employee_id;

---

Connection to Other Concepts

Concept	Why it matters
WHERE	primary location where boolean logic is applied
NULL Values	three-valued logic affects all boolean expressions
IN	often replaces many OR conditions cleanly
BETWEEN	pairs with AND for ranges
Indexes	predicate shape impacts query plans

---

Visual Learning Diagram

flowchart TD
  A[SELECT] --> B[WHERE]
  B --> C[AND / OR / NOT]
  C --> D[Parentheses + Precedence]
  C --> E[NULL (3-valued logic)]
  C --> F[Performance + Indexes]
  D --> G[Correct Filtering]
  E --> G
  F --> H[Query Plan Stability]

  classDef allNodes fill:#3e3e3e,stroke:#ffffff,stroke-width:2px,color:#f5f5f5
  classDef highlight fill:#3e3e3e,stroke:#ffffff,stroke-width:4px,color:#f5f5f5
  class A,B,C,D,E,F,G,H allNodes
  class C highlight

---

Common Pitfalls

Pitfall	Consequence	Prevention
Missing parentheses when mixing AND and OR	wrong rows returned	group intent explicitly with parentheses
Using boolean shorthand on nullable columns	missing NULL rows	use IS TRUE / IS NOT TRUE
Negating without grouping	inverted logic	write NOT (a OR b) instead of NOT a OR b
Overusing broad OR predicates	slow plans	prefer IN, add indexes, or split with UNION ALL
Building filters that are hard to test	regressions	add conditions incrementally and verify each step

---

Quick Reference

-- Precedence: NOT > AND > OR
WHERE a AND b
WHERE a OR b
WHERE NOT a
WHERE a AND (b OR c)

-- Nullable booleans
WHERE flag IS TRUE
WHERE flag IS NOT TRUE

What's Next

• Next: Like Operator - Continue to the next concept with incremental complexity.
• Module Overview - Return to this module index and choose another related lesson.