Filtering Data
AND, OR, and NOT
Combining multiple query filter conditions.
Interview: Logical operator precedence and three-valued logic (TRUE/FALSE/UNKNOWN) are common interview topics. Interviewers test whether you can write complex filters correctly and understand how NULL affects boolean evaluation.
1. Introduction
The AND, OR, and NOT operators combine multiple conditions in the WHERE clause. Understanding their precedence and behavior with NULL values is essential for writing correct filters.
2. Why It Matters
Logical operator precedence and three-valued logic (TRUE/FALSE/UNKNOWN) are common interview topics. Missing parentheses or ignoring NULL behavior causes subtle bugs that return wrong data — or silently exclude rows — in production queries.
3. Real-World Analogy
Logical operators are like search filters on an e-commerce site. AND = "must have both: blue AND size M" (narrows results). OR = "either blue OR red is fine" (broadens results). NOT = "anything except black" (excludes). If you forget parentheses — like searching for "blue OR red AND size M" — the store might show ALL blue items regardless of size.
4. How It Works
- AND: All conditions must be TRUE. Narrows results. Higher precedence than OR.
- OR: At least one condition must be TRUE. Broadens results. Lower precedence than AND.
- NOT: Inverts a boolean expression. Highest precedence. NOT deleted = equivalent to <> 'deleted'.
Precedence order: NOT (1st) → AND (2nd) → OR (3rd). Always use parentheses to make intent explicit.
5. Internal Architecture
SQL uses three-valued logic: every comparison evaluates to TRUE, FALSE, or UNKNOWN. NULL comparisons always produce UNKNOWN. The WHERE clause only returns rows where the overall expression evaluates to TRUE.
6. Visual Explanation
7. Practical Example
8. Common Mistakes
Common Pitfall
Writing WHERE NOT (column = value) when column can be NULL. NULL evaluates to NOT UNKNOWN = UNKNOWN, so the row is excluded. Fix: WHERE column IS NULL OR column <> value.
Interview Insight
Interviewers give ambiguous WHERE clauses and ask "what does this return?" — testing operator precedence knowledge. Always write with explicit parentheses and explain your reasoning out loud.
- Missing parentheses around OR: AND binds tighter, so
a = 1 OR a = 2 AND b = 3≠(a = 1 OR a = 2) AND b = 3. - NULL silently excluded: WHERE column <> value excludes NULL rows. Always consider NULL handling in filters.
9. Quick Quiz
Q1: What does TRUE OR UNKNOWN evaluate to?
TRUE. OR only needs one TRUE side. Even though the other side is UNKNOWN, the TRUE is enough.
Q2: What does TRUE AND UNKNOWN evaluate to?
UNKNOWN. AND needs both sides TRUE. Since one is UNKNOWN, the result is UNKNOWN, and the row is excluded from WHERE.
10. Scenario-Based Challenge
Scenario
A query returns more rows than expected. The WHERE clause is: WHERE status = 'active' OR role = 'admin' AND department = 'Engineering'. What's wrong? How would you fix it to return only active admins OR active engineering users?
11. Debugging Exercise
This query returns fewer rows than expected:
Fix: Rows where category IS NULL are excluded (NOT UNKNOWN = UNKNOWN). To include them: WHERE category IS NULL OR category <> 'electronics'.
12. Interview Questions
Q: What is three-valued logic?
SQL has TRUE, FALSE, and UNKNOWN (from NULL comparisons). WHERE only returns TRUE rows. This means NULL values are silently excluded by most comparison operators.
Q: Why does NOT NULL not return NULL rows?
Because NOT UNKNOWN = UNKNOWN, and WHERE excludes UNKNOWN. To include NULL rows, explicitly use IS NULL or IS NOT NULL.
Q: What's the difference between NOT IN and NOT EXISTS?
NOT IN fails if the subquery returns any NULL values (returns no rows). NOT EXISTS correctly handles NULL — it only checks for the existence of rows, not specific values.
13. Production Considerations
- Dynamic filter building: In application code, build WHERE clauses dynamically with AND separators. Start with WHERE 1=1 then append AND conditions as needed.
- Short-circuit evaluation: PostgreSQL may skip evaluating conditions after finding a FALSE AND or TRUE OR. Put the cheapest/most selective condition first.
- NOT IN with NULLs: Never use NOT IN with subqueries that might return NULL. Use NOT EXISTS instead, or add WHERE column IS NOT NULL to the subquery.
- De Morgan's Laws: NOT (A AND B) = NOT A OR NOT B. NOT (A OR B) = NOT A AND NOT B. Use these to simplify complex negations.
Use Cases
Search filters: Building dynamic WHERE clauses for search functionality where users filter by multiple criteria (status, date range, category)
Access control: Filtering data based on user permissions — WHERE (user_id = current_user OR role = 'admin') AND status <> 'deleted'
Data quality: Finding records that violate business rules using NOT — WHERE NOT (price > 0 AND stock >= 0)
Common Mistakes
Missing parentheses around OR conditions — AND has higher precedence, so WHERE a = 1 OR a = 2 AND b = 3 is NOT the same as WHERE (a = 1 OR a = 2) AND b = 3
Forgetting that NULL comparisons return UNKNOWN — WHERE column <> value silently excludes NULL rows. Always consider NULL handling in filter conditions
Using double negatives that confuse logic — WHERE NOT (status <> 'active') is harder to read than WHERE status = 'active'. Keep conditions positive when possible