Logical Operators

Definition

Logical operators (AND, OR, NOT) combine multiple conditions into a single compound WHERE condition, following three-valued logic (TRUE/FALSE/UNKNOWN) rather than ordinary two-valued boolean logic.

Running example — a products(id, name, category, price, stock) table, where stock can be NULL (not yet counted):

sql
SELECT name FROM products WHERE category = 'Electronics' AND price > 50000;
SELECT name FROM products WHERE category = 'Books' OR category = 'Stationery';
SELECT name FROM products WHERE NOT category = 'Electronics';

AND requires both sides TRUE; OR requires at least one side TRUE; NOT inverts a condition. Because NULL comparisons can produce UNKNOWN, combining logical operators with NULL-involving conditions needs care: TRUE AND UNKNOWN is UNKNOWN (not TRUE), while TRUE OR UNKNOWN is TRUE (the known TRUE side is enough regardless of the other).

Edge Cases and Pitfalls

• AND binds tighter than OR in operator precedence (12.15) — WHERE a OR b AND c means WHERE a OR (b AND c), NOT WHERE (a OR b) AND c; this is a very common source of subtly wrong filter logic when parentheses are omitted.
• NOT (condition involving NULL) doesn't simply "flip" UNKNOWN to a definite value — NOT UNKNOWN is still UNKNOWN, not TRUE, which surprises people expecting NOT to always produce a definite opposite.
• Chaining many ORed equality checks against the same column (category = 'Books' OR category = 'Stationery' OR category = 'Home') is exactly what IN (12.7) exists to express more concisely.

Key Takeaways

• AND/OR/NOT combine conditions, but follow three-valued logic — UNKNOWN doesn't behave like a normal boolean value under NOT.
• AND binds tighter than OR — always parenthesize mixed AND/OR conditions to avoid precedence surprises.
• Many chained OR-equality checks against one column are better expressed with IN.