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.
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.
FROM employees
WHERE city = 'Mumbai'
AND salary > 50000;
Condition B (Salary > 50k): TRUE
Condition B (Salary > 50k): FALSE
The OR Operator
The OR operator expands the result set. A row survives if at least one of the conditions is TRUE:
FROM employees
WHERE city = 'Mumbai'
OR city = 'Delhi';
Condition B (City = 'Delhi'): FALSE
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:
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
Acts like an intersection. Both Condition 1 AND Condition 2 must match.
➔ Returns ONLY Mumbai Sales reps
Acts like a union. Matches if Condition 1 OR Condition 2 is true.
➔ Returns ALL Mumbai employees PLUS all Sales reps anywhere
Combining Multiple Logical Operators & Precedence
You can chain multiple operators together:
WHERE department = 'Sales'
AND salary > 50000
AND city = 'Mumbai';
However, when you mix AND and OR in the same statement without grouping:
WHERE city = 'Mumbai' OR city = 'Delhi' AND salary > 50000;
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:
SELECT * FROM employees
WHERE (city = 'Mumbai' OR city = 'Delhi')
AND salary > 50000;
AND and OR, always use parentheses around your OR clauses. This guarantees deterministic behavior across every database engine and makes your code self-documenting.(city = 'Mumbai' OR city = 'Delhi')
(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 > 50000when salary is NULL.
Row Kept in Result Set
Row Dropped
Row Dropped
| 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) |
| name | department | city | salary |
|---|---|---|---|
| Rahul Sharma | Sales | Mumbai | ₹62,000 |
| Rohan Joshi | Sales | Bengaluru | ₹71,000 |
Common Logical Operator Mistakes
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.
Writing WHERE city = 'Mumbai' OR city = 'Delhi' AND active = 1 accidentally keeps all Mumbai records even if they are inactive.
Writing WHERE department NOT = 'HR' is invalid SQL syntax. Use WHERE NOT department = 'HR' or WHERE department <> 'HR'.
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/ORkeyword on a new indented line. - Prefer <> Over NOT Column =: Direct inequality operators (
department <> 'HR') are often cleaner thanNOT 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.