Logical Operators in SQL: AND, OR, NOT with Complex Conditions

Real-world filtering questions are rarely as simple as one condition — 'electronics under $500 that are currently in stock' already involves three conditions chained together. AND, OR, and NOT are SQL's logical operators, letting WHERE clauses express exactly this kind of layered, real-world logic.

What Are Logical Operators?

Logical operators combine multiple boolean conditions within a single WHERE clause. AND requires every combined condition to be true for a row to be included. OR requires at least one of the combined conditions to be true. NOT inverts a condition's result, turning true into false and vice versa.

What You'll Learn

• Combine multiple WHERE conditions using AND, OR, and NOT.
• Understand operator precedence between AND and OR.
• Use parentheses to control evaluation order explicitly.
• Write correctly grouped complex multi-condition filters.

Key Terms to Know

• AND: A logical operator requiring all combined conditions to be true.
• OR: A logical operator requiring at least one combined condition to be true.
• NOT: A logical operator that inverts a condition's boolean result.
• Operator precedence: The order in which AND and OR are evaluated when both appear without explicit parentheses.

AND: Requiring Every Condition to Be True

WHERE category = 'electronics' AND price < 500; returns only rows satisfying both conditions simultaneously — a product must be in the electronics category and priced under 500; failing either condition excludes the row. Adding more AND conditions only narrows the result further, since every additional condition must also be satisfied.

OR: Requiring At Least One Condition to Be True

WHERE category = 'electronics' OR category = 'appliances'; returns rows satisfying either condition, widening the result compared to AND. A common real-world use is checking a column against several acceptable values, though the IN operator (covered in the next lesson) is often a cleaner way to express that specific pattern.

NOT and Operator Precedence with Parentheses

NOT inverts a condition: WHERE NOT (status = 'cancelled'); is equivalent to WHERE status != 'cancelled';. NOT is most useful when negating a more complex condition that would be awkward to rewrite directly, such as WHERE NOT (price > 100 AND category = 'books');.

MySQL evaluates AND before OR by default when both appear in the same WHERE clause without parentheses, which can produce unexpected results. WHERE category = 'electronics' OR category = 'books' AND price < 50; actually means 'electronics of any price, OR books under 50' — not 'electronics or books, both under 50'. Adding explicit parentheses, like WHERE (category = 'electronics' OR category = 'books') AND price < 50;, removes any ambiguity and matches the intended logic.

Visual Summary

Picture a security checkpoint with two separate gates representing two conditions. AND means a person must pass through BOTH gates to be allowed in — failing either one stops them. OR means a person only needs to pass through AT LEAST ONE of the two gates. NOT flips the result of whatever gate check follows it, turning an approval into a rejection and vice versa. Parentheses are like fencing that groups certain gates together so it's unambiguous which gates belong to which checkpoint group.

Logical Operators at a Glance

Operator	Requires	Example
AND	All combined conditions true	WHERE category='electronics' AND price<500
OR	At least one condition true	WHERE category='electronics' OR category='appliances'
NOT	Inverts a condition's result	WHERE NOT (status='cancelled')

SQL Example

-- AND: products that are both in electronics AND under 500
SELECT product_name, category, price
FROM products
WHERE category = 'electronics' AND price < 500;

-- OR: products in either electronics or appliances
SELECT product_name, category
FROM products
WHERE category = 'electronics' OR category = 'appliances';

-- NOT: products that are NOT cancelled-status orders
SELECT order_id, status
FROM orders
WHERE NOT (status = 'cancelled');

-- Combining AND/OR correctly with parentheses
SELECT product_name, category, price
FROM products
WHERE (category = 'electronics' OR category = 'books')
  AND price < 50;

The first two queries show straightforward AND and OR usage. The third demonstrates NOT inverting a simple equality check. The fourth is the most important: explicit parentheses ensure the OR condition between categories is evaluated as a single group before being combined with the price condition via AND, avoiding the silent logic error that would occur without them.

Real-World Examples

• E-commerce filter sidebars combine AND across multiple filter types (category AND price range AND in-stock) while using OR within a single filter type (size = 'M' OR size = 'L').
• Fraud detection systems use complex AND/OR/NOT combinations to flag transactions matching several risk patterns simultaneously.
• Customer segmentation queries combine conditions like (country = 'IN' OR country = 'US') AND signup_date > '2025-01-01' to build precise audience lists.
• Content moderation tools use NOT to exclude already-reviewed or already-approved content from a moderation queue.

Best Practices and Pro Tips

• Always use parentheses explicitly whenever a WHERE clause mixes AND and OR together, even if you've correctly worked out the default precedence — it removes any ambiguity for the next person reading the query.
• Break very long, deeply nested AND/OR/NOT conditions into well-commented, clearly grouped sections, or consider using a view or CTE to simplify an overly complex filter.
• When checking a column against many possible values with repeated OR conditions, consider the IN operator instead, which is often more readable for that specific pattern.

Common Mistakes to Avoid

• Mixing AND and OR without parentheses and assuming the query reads top-to-bottom, left-to-right, when MySQL actually evaluates AND before OR by default.
• Using NOT in a confusing way that's harder to read than simply rewriting the condition directly, such as preferring status != 'cancelled' over NOT (status = 'cancelled') when both work identically.
• Forgetting that NOT applied to a complex parenthesized condition inverts the entire group, not just the first sub-condition inside it.
• Writing overly long chains of OR conditions for the same column instead of using the more readable IN operator.

Interview Questions

Q1. What is the default evaluation precedence between AND and OR in SQL?

AND is evaluated before OR by default when both appear in the same condition without parentheses, which can produce results different from what a left-to-right reading might suggest.

Q2. Why should parentheses be used even when you know the default AND/OR precedence?

Explicit parentheses remove all ambiguity for anyone else reading or maintaining the query later, making the intended logic immediately clear without requiring them to recall or verify operator precedence rules.

Q3. What does the NOT operator do in a WHERE clause?

NOT inverts the boolean result of the condition that follows it, turning a true result into false and a false result into true, effectively reversing which rows are included.

Q4. How would you rewrite WHERE NOT (status = 'cancelled') using a direct comparison operator instead?

WHERE status != 'cancelled' (or equivalently WHERE status <> 'cancelled') achieves the exact same result more directly, without needing NOT at all.

Practice MCQs

1. Without parentheses, which operator does MySQL evaluate first: AND or OR?

1. OR is always evaluated first
2. AND is evaluated first
3. They are evaluated strictly left to right
4. MySQL throws an error if both appear together

Answer: B. AND is evaluated first

Explanation: MySQL follows standard SQL precedence rules where AND binds tighter than OR, evaluating AND conditions before OR conditions by default.

2. Which condition correctly returns rows in electronics under 500, using AND?

1. WHERE category = 'electronics' OR price < 500
2. WHERE category = 'electronics' AND price < 500
3. WHERE NOT category = 'electronics'
4. WHERE category != 'electronics'

Answer: B. WHERE category = 'electronics' AND price < 500

Explanation: AND requires both the category and price conditions to be true simultaneously for a row to be included.

3. What is the safest way to avoid AND/OR precedence confusion in a complex WHERE clause?

1. Avoid using OR entirely
2. Always use parentheses to group conditions explicitly
3. Always put AND conditions first
4. Use NOT instead of AND or OR

Answer: B. Always use parentheses to group conditions explicitly

Explanation: Explicit parentheses remove any ambiguity about evaluation order, regardless of default precedence rules.

Quick Revision Points

• AND requires all conditions true; OR requires at least one; NOT inverts a condition.
• MySQL evaluates AND before OR by default without explicit parentheses.
• Parentheses should always be used when mixing AND and OR to avoid ambiguity.
• NOT applied to a parenthesized group inverts the entire group's result.

Conclusion

• AND, OR, and NOT let WHERE clauses express genuinely complex, real-world filtering logic.
• Operator precedence is a real and common source of subtle bugs without explicit parentheses.
• Clear, well-parenthesized conditions are a readability investment that pays off immediately for any future reader.

AND, OR, and NOT extend the WHERE clause from simple single-condition filtering into genuinely complex, multi-condition logic that mirrors real-world questions. Understanding that AND is evaluated before OR by default — and using explicit parentheses to remove any doubt — prevents one of the most common subtle bugs in SQL filtering. The next lesson covers BETWEEN, a convenient shorthand specifically for range-based filtering on numbers and dates.