Skip to content
Tutorials Center
My learning

Topics

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.

SQL
SELECT title, genre, price
FROM books
WHERE (genre = 'Fiction' OR genre = 'Mystery') AND price < 15;

Try it

SQL Precedence: AND before OR ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

SQL With parentheses the meaning changes ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

SQL Truth tables with NULL ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
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 AND and OR appear 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 > 1 to avoid a division by zero. Use CASE to enforce order.
  • NOT and NULL. NOT (col = 'x') drops rows where col is NULL, like col <> 'x'. Add OR col IS NULL to keep them.
  • De Morgan's laws: NOT (a AND b) equals NOT a OR NOT b, and NOT (a OR b) equals NOT a AND NOT b. These hold in three-valued logic too.
  • Prefer positive predicates. NOT IN, NOT LIKE, NOT BETWEEN and IS NOT NULL are clearer than wrapping with NOT (...).
  • Chained OR on one column reads better as IN: genre IN ('Fiction', 'Mystery').
  • Performance. Many OR branches on different columns can prevent index use; a UNION ALL of two selective queries is sometimes faster.