Pathubs SQL Curriculum • Module 04

SQL AND / OR / NOT

Master complex boolean row filtering: understand conjunction (AND), disjunction (OR), negation (NOT), truth tables, operator precedence, parentheses grouping, and SQL three-valued logic.

⏱️ Estimated Time:40 Minutes

🎯 Level:Beginner to Intermediate

📊 Track:Data Analytics & SQL Mastery

✨ Mode:Live Boolean Logic Lab

Introduction

In real-world data analysis, a single condition is rarely enough. A marketing team might need customers who live in Mumbai AND spent over ₹5,000. A human resources department might search for employees in Engineering OR Sales who joined after 2024.

SQL provides logical operators (AND, OR, NOT) to stitch multiple individual criteria into robust, expressive query filters.

SELECT *
FROM employees
WHERE department = 'Sales'
AND salary > 50000;

What Are Logical Operators?

Logical operators are SQL keywords that evaluate one or more boolean sub-expressions and yield an overall boolean result:

Logical Operator	Core Meaning	Requirement for Row to Pass
`AND`	Conjunction (All)	Every combined condition must be TRUE
`OR`	Disjunction (Any)	At least one condition must be TRUE
`NOT`	Negation (Inversion)	The underlying condition must evaluate to FALSE

The AND Operator

The AND operator narrows down query results. A row is included only if all conditions connected by AND evaluate to TRUE. If even one sub-condition evaluates to FALSE, the candidate row is immediately dropped.

SELECT name, salary, city
FROM employees
WHERE city = 'Mumbai'
AND salary > 50000;

Diagram 1: AND Logic Evaluation

Condition A (City = 'Mumbai'): TRUE
Condition B (Salary > 50k): TRUE

➔ AND ➔

Row KEPT (TRUE)

Condition A (City = 'Mumbai'): TRUE
Condition B (Salary > 50k): FALSE

➔ AND ➔

Row DROPPED (FALSE)

The OR Operator

The OR operator expands the result set. A row survives if at least one of the conditions is TRUE:

SELECT name, city
FROM employees
WHERE city = 'Mumbai'
OR city = 'Delhi';

Diagram 2: OR Logic Evaluation

Condition A (City = 'Mumbai'): TRUE
Condition B (City = 'Delhi'): FALSE

➔ OR ➔

Row KEPT (TRUE)

The NOT Operator

The NOT operator reverses the truth value of a condition. If an evaluation is TRUE, NOT makes it FALSE. If an evaluation is FALSE, NOT makes it TRUE:

SELECT name, department
FROM employees
WHERE NOT department = 'HR';

This returns all employees who work in Sales, Engineering, or any department other than HR.

AND vs OR — The Most Important Difference

🔒 AND (Restrictive Filter)

Acts like an intersection. Both Condition 1 AND Condition 2 must match.

WHERE city = 'Mumbai' AND dept = 'Sales'
➔ Returns ONLY Mumbai Sales reps

🔓 OR (Expansive Filter)

Acts like a union. Matches if Condition 1 OR Condition 2 is true.

WHERE city = 'Mumbai' OR dept = 'Sales'
➔ Returns ALL Mumbai employees PLUS all Sales reps anywhere

Combining Multiple Logical Operators & Precedence

You can chain multiple operators together:

SELECT * FROM employees
WHERE department = 'Sales'
AND salary > 50000
AND city = 'Mumbai';

However, when you mix AND and OR in the same statement without grouping:

SELECT * FROM employees
WHERE city = 'Mumbai' OR city = 'Delhi' AND salary > 50000;

⚠️

Operator Precedence Rule: In standard SQL, AND has higher precedence than OR. SQL evaluates city = 'Delhi' AND salary > 50000 first, and then ORs the result with city = 'Mumbai'. This means all Mumbai employees will be returned regardless of their salary!

Parentheses and Logical Grouping

To override default precedence and ensure your query does exactly what you intended, wrap your logical groups in parentheses:

-- ✅ EXPLICIT GROUPING: Salary > 50k applies to BOTH cities
SELECT * FROM employees
WHERE (city = 'Mumbai' OR city = 'Delhi')
AND salary > 50000;

💡

Golden Rule of SQL Logic: Whenever a query mixes AND and OR, always use parentheses around your OR clauses. This guarantees deterministic behavior across every database engine and makes your code self-documenting.

Diagram 3: Parentheses Evaluation Order

Step 1: Evaluate (A OR B)
(city = 'Mumbai' OR city = 'Delhi')

➔

Step 2: Combine with AND C
(Result) AND (salary > 50000)

Understanding Truth Tables

Truth tables show how boolean values combine under logical operations:

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

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

SQL's Three-Valued Logic — Basic Introduction

Unlike traditional computer science where booleans are strictly TRUE or FALSE, SQL implements three-valued logic:

TRUE: The condition is confirmed true.
FALSE: The condition is confirmed false.
UNKNOWN: The condition involves a NULL (missing value). For example, salary > 50000 when salary is NULL.

ℹ️

WHERE Rule for UNKNOWN: A row is returned only when the final condition evaluates strictly to TRUE. Both FALSE and UNKNOWN evaluate as non-matching rows and are excluded from the result set.

Diagram 4: SQL Three-Valued Logic Outcomes

TRUE
Row Kept in Result Set

FALSE
Row Dropped

UNKNOWN (NULL)
Row Dropped

⚡ Live Interactive Logic Filter Lab

📦 Candidate Row Boolean Evaluation8 Total Records

Name	Dept	City	Salary	Boolean Evaluation Breakdown
Rahul Sharma	Sales	Mumbai	₹62,000	(Dept = 'Sales': TRUE) AND (Salary > 60k: TRUE) ➔ TRUE (Keep)
Priya Patel	HR	Delhi	₹48,000	(Dept = 'Sales': FALSE) AND (Salary > 60k: FALSE) ➔ FALSE (Drop)
Amit Verma	Engineering	Bengaluru	₹85,000	(Dept = 'Sales': FALSE) AND (Salary > 60k: TRUE) ➔ FALSE (Drop)
Sneha Rao	Sales	Mumbai	₹54,000	(Dept = 'Sales': TRUE) AND (Salary > 60k: FALSE) ➔ FALSE (Drop)
Vikram Singh	Engineering	Delhi	₹92,000	(Dept = 'Sales': FALSE) AND (Salary > 60k: TRUE) ➔ FALSE (Drop)
Ananya Gupta	HR	Pune	₹45,000	(Dept = 'Sales': FALSE) AND (Salary > 60k: FALSE) ➔ FALSE (Drop)
Rohan Joshi	Sales	Bengaluru	₹71,000	(Dept = 'Sales': TRUE) AND (Salary > 60k: TRUE) ➔ TRUE (Keep)
Kavita Nair	Engineering	Mumbai	₹68,000	(Dept = 'Sales': FALSE) AND (Salary > 60k: TRUE) ➔ FALSE (Drop)

✍️ SQL Logical Query Editor● Live Parser

🔍 Test The Parentheses Difference:

📋 Output Result Set2 Rows Kept (6 Dropped)

name	department	city	salary
Rahul Sharma	Sales	Mumbai	₹62,000
Rohan Joshi	Sales	Bengaluru	₹71,000

Common Logical Operator Mistakes

1. Writing Impossible AND Conditions on the Same Column

WHERE city = 'Mumbai' AND city = 'Delhi' returns 0 rows because an employee cannot reside in two different cities at the same time. Use OR instead.

2. Forgetting Parentheses Around OR in Mixed Queries

Writing WHERE city = 'Mumbai' OR city = 'Delhi' AND active = 1 accidentally keeps all Mumbai records even if they are inactive.

3. Misunderstanding NOT Placement

Writing WHERE department NOT = 'HR' is invalid SQL syntax. Use WHERE NOT department = 'HR' or WHERE department <> 'HR'.

🧠 Interactive Challenge: Predict The Result (1 of 3)

SELECT name FROM employees WHERE department = 'Sales' AND city = 'Mumbai';

Which employees will be returned by this AND query?

Practical AND / OR / NOT Exercises

Task Goal	Target Table	Required SQL Solution	Concept Tested
1. Mumbai Sales Reps	`employees`	`SELECT * FROM employees WHERE city = 'Mumbai' AND department = 'Sales';`	Strict 2-way AND conjunction
2. Multi-City Selection	`customers`	`SELECT * FROM customers WHERE city = 'Mumbai' OR city = 'Delhi';`	OR disjunction
3. Exclude HR Dept	`employees`	`SELECT * FROM employees WHERE NOT department = 'HR';`	NOT negation
4. High Earners in Key Hubs	`employees`	`SELECT * FROM employees WHERE (city = 'Mumbai' OR city = 'Bengaluru') AND salary >= 70000;`	Explicit parentheses grouping
5. Triple Condition Filter	`orders`	`SELECT * FROM orders WHERE status = 'Completed' AND total > 1000 AND year = 2026;`	Multi-clause AND chain

Logical Operator Best Practices

Always Use Parentheses When Mixing AND & OR: Never rely on memory or implicit database precedence rules.
Format One Condition per Line: For queries with 3+ criteria, place each AND / OR keyword on a new indented line.
Prefer <> Over NOT Column =: Direct inequality operators (department <> 'HR') are often cleaner than NOT department = 'HR'.
Keep Logic Readable: Simplify boolean formulas before writing SQL to minimize unnecessary complexity.

What You Should Know Now

✓AND: All conditions must evaluate to TRUE
✓OR: At least one condition must evaluate to TRUE
✓NOT: Reverses boolean truth values
✓Precedence: AND takes precedence over OR
✓Parentheses: Required for clear, deterministic grouping
✓3-Valued Logic: UNKNOWN (NULL) rows are excluded by WHERE

🎯 Knowledge Check Quiz: SQL AND / OR / NOT

Test your understanding of logical operators, truth tables, parentheses grouping, and operator precedence.

1. In SQL logical evaluation, when does an "AND" condition evaluate to TRUE?

2. In SQL logical evaluation, when does an "OR" condition evaluate to TRUE?

3. What is standard SQL operator precedence when mixing AND and OR without parentheses?

4. Why does "WHERE (city = 'Mumbai' OR city = 'Delhi') AND salary > 50000" differ from "WHERE city = 'Mumbai' OR city = 'Delhi' AND salary > 50000"?

5. What does the NOT operator do to a boolean condition?

6. Under SQL three-valued logic, what does "TRUE AND UNKNOWN" evaluate to?

7. Given rows with department [Sales, HR, Engineering], which rows survive "WHERE NOT department = 'HR'"?

8. What is the best practice recommendation when combining multiple AND and OR operators in production queries?