SQL Reference ยท Operators and Predicates
SQL AND, OR and NOT
Reference for the SQL logical operators AND, OR and NOT: truth tables with NULL, operator precedence, parentheses and common filter mistakes.
AND, OR and NOT combine conditions into one. a AND b is true when both are true, a OR b when at least one is, and NOT a flips a condition. Because SQL has three truth values (true, false and unknown), the operators follow a three-valued logic that matters whenever NULL is involved.
Precedence is the other thing to know: NOT binds tighter than AND, which binds tighter than OR. Mixing AND and OR without parentheses is a very common source of wrong results.
SELECT title, genre, price FROM books WHERE (genre = 'Fiction' OR genre = 'Mystery') AND price < 15;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
condition AND condition condition OR condition NOT condition
| a | b | a AND b | a OR b |
|---|---|---|---|
| TRUE | TRUE | TRUE | TRUE |
| TRUE | FALSE | FALSE | TRUE |
| TRUE | NULL | NULL | TRUE |
| FALSE | FALSE | FALSE | FALSE |
| FALSE | NULL | FALSE | NULL |
| NULL | NULL | NULL | NULL |
NOT TRUE is FALSE, NOT FALSE is TRUE, NOT NULL is NULL. A WHERE or ON clause keeps a row only when its condition is TRUE; both FALSE and NULL drop it.
Precedence, highest first: comparison operators and predicates (=, BETWEEN, IN, LIKE, IS NULL), then NOT, then AND, then OR.
Dialect differences
| Database | Notes |
|---|---|
| SQLite | Booleans are the integers 1 and 0; any non-zero number is true in a condition. |
| PostgreSQL | Real boolean type; WHERE 1 is an error. |
| MySQL | &&, ` |
| SQL Server | No boolean type: conditions cannot be stored or selected directly; use CASE. |
| Oracle | Same, until the BOOLEAN type of 23ai; conditions are not values. |
Usage notes
- Use parentheses whenever
ANDandORappear together, even when the default precedence matches your intent. It documents the logic and prevents mistakes during edits. - Short-circuiting is not guaranteed. The optimizer may evaluate conditions in any order, so do not rely on
x <> 0 AND 10 / x > 1to avoid a division by zero. UseCASEto enforce order. NOTandNULL.NOT (col = 'x')drops rows wherecolisNULL, likecol <> 'x'. AddOR col IS NULLto keep them.- De Morgan's laws:
NOT (a AND b)equalsNOT a OR NOT b, andNOT (a OR b)equalsNOT a AND NOT b. These hold in three-valued logic too. - Prefer positive predicates.
NOT IN,NOT LIKE,NOT BETWEENandIS NOT NULLare clearer than wrapping withNOT (...). - Chained
ORon one column reads better as IN:genre IN ('Fiction', 'Mystery'). - Performance. Many
ORbranches on different columns can prevent index use; aUNION ALLof two selective queries is sometimes faster.