AND, OR and NOT

Combining conditions in MySQL 8.4: precedence order, when parentheses are mandatory, and how NULL moves through each operator.

Syntaxcond AND cond | cond OR cond | NOT cond -- precedence: NOT, then AND, then XOR, then OR

AND, OR and NOT combine the 1/0/NULL results of individual conditions into one verdict per row. MySQL treats any nonzero, non-NULL value as TRUE, so WHERE 2 is a valid (if useless) filter.

Shorthand and longhand

&& is AND, || is OR, and ! is NOT. All three are nonstandard extensions that have been deprecated since MySQL 8.0.17 and remain deprecated in 8.4; expect them to be removed. Use the word forms in new code and recognise the symbols in old code. || is not deprecated when the PIPES_AS_CONCAT SQL mode is on, because there it means string concatenation instead.

Precedence

From tightest to loosest: comparison operators (=, <>, IS, LIKE, REGEXP, IN), then BETWEEN and CASE, then NOT, then AND, then XOR, then OR. Because AND binds tighter than OR, a = 1 OR a = 2 AND b = 3 parses as a = 1 OR (a = 2 AND b = 3). Parenthesise any mixed expression, even where you are sure.

NULL propagation

AND is 0 if any operand is 0, 1 if all are nonzero and non-NULL, and NULL otherwise. OR is 1 if any operand is nonzero, and NULL when the only other operand is NULL or false-unknown. NOT NULL is NULL, and XOR with a NULL operand is always NULL. So 0 AND NULL is 0 and 1 OR NULL is 1: unknown does not always win.

Example

Example · sql
-- Intent: active or trialing accounts, in either of two regions.

-- Wrong. AND binds tighter than OR, so this parses as:
--   status = 'active' OR (status = 'trialing' AND region IN ('eu','uk'))
-- Active accounts from every region leak through.
SELECT id, status, region
FROM accounts
WHERE status = 'active' OR status = 'trialing'
  AND region IN ('eu', 'uk');

-- Right: parenthesise the OR group.
SELECT id, status, region
FROM accounts
WHERE (status = 'active' OR status = 'trialing')
  AND region IN ('eu', 'uk');

When to use it

  • Combine a status filter with a date range without the AND/OR grouping silently widening the result
  • Translate a deprecated && or || from an older codebase into standard AND/OR
  • Work out why NOT (price > 100) keeps fewer rows than price <= 100 on a nullable column
  • Build an admin search where several optional filters are ANDed together

More examples

The deprecated symbol forms

MySQL 8.4 still accepts &&, || and !, but the manual marks all three as nonstandard extensions slated for removal. Read them fluently in inherited queries, then replace them. Watch for || in a codebase that enables PIPES_AS_CONCAT: there it concatenates strings and has nothing to do with OR.

Example · sql
-- These pairs are identical in meaning.
SELECT * FROM logs WHERE level = 'error' AND retries > 0;
SELECT * FROM logs WHERE level = 'error' &&  retries > 0;   -- deprecated

SELECT * FROM logs WHERE level = 'error' OR retries > 0;
SELECT * FROM logs WHERE level = 'error' || retries > 0;    -- deprecated

SELECT NOT 0, ! 0;   -- 1, 1  -- ! is deprecated too

Truth table for NULL operands

Short-circuit logic still applies to unknowns: a single FALSE settles an AND, and a single TRUE settles an OR. Everything else stays NULL, and a NULL condition drops the row. This is why adding NOT around a condition is not the same as negating the comparison inside it when nullable columns are involved.

Example · sql
SELECT 1 AND NULL,   -- NULL: could be true, could be false
       0 AND NULL,   -- 0:    false regardless of the unknown
       1 OR  NULL,   -- 1:    true regardless of the unknown
       0 OR  NULL,   -- NULL
       NOT NULL,     -- NULL
       1 XOR NULL;   -- NULL

NOT applies to the whole comparison

Use this when a reviewer asks whether NOT x > y needs parentheses: it does not, because comparison operators bind tighter than NOT. The example also shows the more common bug behind it, which is assuming a negated filter will pick up NULL rows.

Example · sql
-- NOT sits below the comparison operators in precedence,
-- so it takes the entire comparison as its operand.
SELECT * FROM items WHERE NOT price > 100;   -- NOT (price > 100)

-- Equivalent for non-NULL prices, but clearer:
SELECT * FROM items WHERE price <= 100;

-- Not equivalent when price IS NULL: both forms return NULL and
-- drop the row, so neither one finds unpriced items.
SELECT * FROM items WHERE price <= 100 OR price IS NULL;

Related

sql wheresql comparisonsql nullsql insql case

Discussion

  • Be the first to comment on this lesson.