Operator Precedence

This page lists operator precedence used by the parser and evaluator. Operators are evaluated in order from highest precedence (top) to lowest precedence (bottom).

Precedence Order

Precedence	Operator(s)	Category	Description
1 (Highest)	`(...)`	Parentheses	Grouping and explicit precedence
2	`-`, `+`, `NOT`	Unary	Negation, unary plus, logical NOT
3	`*`, `/`, `%`	Multiplicative	Multiplication, division, modulo
4	`+`, `-`	Additive	Addition, subtraction
5	`<`, `<=`, `>`, `>=`	Comparison	Less than, less or equal, greater than, greater or equal
6	`=`, `!=`, `<>`	Equality	Equal, not equal
7	`BETWEEN`, `IN`, `LIKE`, `RLIKE`	Membership & Pattern	Range, set membership, pattern matching
7	`EXISTS`	Existential	True when the subquery returns at least one row
8	`AND`	Logical AND	Logical conjunction
9 (Lowest)	`OR`	Logical OR	Logical disjunction

Since 0.24.0 a condition that combines AND and OR without parentheses is evaluated with this precedence in every clause (WHERE, HAVING, CASE WHEN, DELETE, UPDATE); earlier versions could evaluate it in another order and return other rows without an error.

1. Parentheses `(...)`

Highest precedence - Used to explicitly control evaluation order.

Examples:

-- Without parentheses
SELECT 1 + 2 * 3 AS v;
-- Result: 7 (multiplication first: 1 + 6)

-- With parentheses
SELECT (1 + 2) * 3 AS v;
-- Result: 9 (addition first: 3 * 3)

-- Complex expression
SELECT 10 - 2 * 3 AS v1,
       (10 - 2) * 3 AS v2;
-- v1 = 4 (10 - 6)
-- v2 = 24 (8 * 3)

-- Multiple levels
SELECT ((5 + 3) * 2 - 4) / 2 AS result;
-- Result: 6
-- Step 1: (5 + 3) = 8
-- Step 2: 8 * 2 = 16
-- Step 3: 16 - 4 = 12
-- Step 4: 12 / 2 = 6

Best Practice:

-- Use parentheses for clarity even when not required
SELECT price * (1 + tax_rate) AS total_price
FROM products;

-- Better than ambiguous:
SELECT price * 1 + tax_rate AS total_price  -- Unclear intent
FROM products;

2. Unary Operators: `-`, `+`, `NOT`

Second highest precedence - Applied to single operands.

Examples:

Unary Minus (Negation):

-- Negate a value
SELECT -5 AS neg_value;
-- Result: -5

-- Negate column value
SELECT product_id, -price AS negative_price
FROM products;

-- In calculations
SELECT 10 + -5 AS result;
-- Result: 5

-- With parentheses for clarity
SELECT 10 + (-5) AS result;
-- Result: 5

-- Double negation: subtract the negative value (a unary minus before a parenthesis does not
-- parse)
SELECT 0 - (-10) AS result;
-- Result: 10

Unary Plus:

-- ✗ parse error: a unary plus is not supported -- write the value itself
SELECT +5 AS pos_value;

SELECT +price AS positive_price
FROM products;

Logical NOT:

-- NOT negates the condition right after it
SELECT * FROM products
WHERE NOT price > 100;
-- Same as: WHERE price <= 100

-- NOT binds tighter than AND and OR
SELECT * FROM users
WHERE NOT is_active = true AND is_verified = true;
-- Evaluated as: (NOT is_active = true) AND (is_verified = true)

-- IN, LIKE, RLIKE, BETWEEN and IS NULL take their NOT after the column
SELECT * FROM orders
WHERE status NOT IN ('cancelled', 'refunded');

NOT applies to one condition. It is not accepted before a parenthesised group (NOT (a = 1 OR b = 2)) or a bare boolean column (NOT is_active): write the condition De Morgan’s laws give (NOT a = 1 AND NOT b = 2), or compare the column (is_active = false). See Known Limitations.

3. Multiplicative: `*`, `/`, `%`

Third precedence - Multiplication, division, and modulo operations.

Examples:

Multiplication:

-- Basic multiplication
SELECT 5 * 3 AS result;
-- Result: 15

-- In expressions
SELECT price * quantity AS total
FROM order_items;

-- Multiple multiplications (left to right)
SELECT 2 * 3 * 4 AS result;
-- Result: 24 (evaluated as (2 * 3) * 4)

Division:

-- Basic division
SELECT 10 / 2 AS result;
-- Result: 5.0

-- `/` is ALWAYS floating-point, whatever its operands are (since 0.24.0)
SELECT 10 / 3 AS result;
-- Result: 3.3333333333333335

SELECT 10.0 / 3 AS result;
-- Result: 3.333...

-- Division by zero needs no guard of your own: it is NULL
SELECT total / quantity AS avg_price FROM orders;
-- NULL on the rows where quantity = 0

A CASE or COALESCE wrapper around a division does not parse — see Known Limitations.

See / in the operators reference for the full rule, including why a CASE or COALESCE wrapper around a division does not parse.

Modulo:

-- Basic modulo
SELECT 10 % 3 AS remainder;
-- Result: 1

-- Even/odd check
SELECT
  number,
  CASE WHEN number % 2 = 0 THEN 'Even' ELSE 'Odd' END AS parity
FROM numbers;

-- Cycling values
SELECT
  day_number,
  day_number % 7 AS day_of_week
FROM calendar;

Mixed Operations:

-- Multiplication and division (left to right)
SELECT 10 * 2 / 4 AS result;
-- Result: 5 (evaluated as (10 * 2) / 4 = 20 / 4)

-- With modulo
SELECT 17 % 5 * 2 AS result;
-- Result: 4 (evaluated as (17 % 5) * 2 = 2 * 2)

4. Additive: `+`, `-`

Fourth precedence - Addition and subtraction operations.

Examples:

Addition:

-- Basic addition
SELECT 5 + 3 AS result;
-- Result: 8

-- Multiple additions
SELECT 10 + 20 + 30 AS result;
-- Result: 60

-- With columns
SELECT
  base_price + tax + shipping AS total_cost
FROM orders;

Subtraction:

-- Basic subtraction
SELECT 10 - 3 AS result;
-- Result: 7

-- Multiple subtractions (left to right)
SELECT 100 - 20 - 10 AS result;
-- Result: 70 (evaluated as (100 - 20) - 10)

-- Date arithmetic
SELECT
  order_date,
  DATE_SUB(order_date, INTERVAL 7 DAY) AS week_ago
FROM orders;

Mixed with Multiplicative:

-- Multiplication before addition
SELECT 1 + 2 * 3 AS result;
-- Result: 7 (evaluated as 1 + (2 * 3) = 1 + 6)

-- Use parentheses to change order
SELECT (1 + 2) * 3 AS result;
-- Result: 9

-- Complex expression
SELECT 10 + 5 * 2 - 3 AS result;
-- Result: 17 (evaluated as 10 + (5 * 2) - 3 = 10 + 10 - 3)

-- With parentheses
SELECT (10 + 5) * (2 - 3) AS result;
-- Result: -15 (evaluated as 15 * (-1))

5. Comparison: `<`, `<=`, `>`, `>=`

Fifth precedence - Relational comparisons.

Examples:

Less Than / Greater Than:

-- Basic comparisons (a comparison is a condition: in the SELECT list, write it in a CASE)
SELECT CASE WHEN 5 < 10 THEN 1 ELSE 0 END AS result;
-- Result: 1

SELECT CASE WHEN 5 > 10 THEN 1 ELSE 0 END AS result;
-- Result: 0

-- In WHERE clause
SELECT * FROM products
WHERE price < 100;

SELECT * FROM users
WHERE age >= 18;

With Arithmetic:

-- Arithmetic evaluated first
SELECT * FROM products
WHERE price + tax > 100;
-- Evaluated as: (price + tax) > 100

SELECT * FROM orders
WHERE quantity * price < 1000;
-- Evaluated as: (quantity * price) < 1000

Multiple Comparisons:

-- Chained comparisons require AND
SELECT * FROM products
WHERE price > 50 AND price < 100;

-- NOT this (syntax error in most SQL):
-- WHERE 50 < price < 100

-- Use BETWEEN instead
SELECT * FROM products
WHERE price BETWEEN 50 AND 100;

6. Equality: `=`, `!=`, `<>`

Sixth precedence - Equality and inequality checks.

Examples:

Equality:

-- Basic equality (a comparison is a condition: in the SELECT list, write it in a CASE)
SELECT CASE WHEN 5 = 5 THEN 1 ELSE 0 END AS result;
-- Result: 1

-- In WHERE clause
SELECT * FROM users
WHERE status = 'active';

-- NULL handling
SELECT * FROM users
WHERE email = NULL;  -- Always false!
-- Use: WHERE email IS NULL

Inequality:

-- Not equal (two forms)
SELECT CASE WHEN 5 != 3 THEN 1 ELSE 0 END AS result;
-- Result: 1

SELECT CASE WHEN 5 <> 3 THEN 1 ELSE 0 END AS result;
-- Result: 1

-- In WHERE clause
SELECT * FROM orders
WHERE status != 'cancelled';

SELECT * FROM products
WHERE category <> 'discontinued';

With Comparisons:

-- ✗ parse error: a comparison is not compared with a boolean, with or without parentheses
SELECT * FROM products
WHERE price > 50 = true;

SELECT * FROM products
WHERE (price > 50) = true;

-- Write the comparison itself:
SELECT * FROM products
WHERE price > 50;

7. Membership & Pattern: `BETWEEN`, `IN`, `LIKE`, `RLIKE`

Seventh precedence - Range, set membership, and pattern matching.

A subquery does not change the level of its operator. IN (SELECT …) binds exactly where IN (1, 2, 3) binds; x > (SELECT AVG(…) …) and the quantified forms x > ALL (SELECT …) / x >= ANY (SELECT …) bind at the comparison level (5), and = ANY | SOME / <> ALL at the equality level (6) — where they are normalised to IN / NOT IN. EXISTS (SELECT …) is a unary predicate taking no left operand, so nothing binds to its left. All of them are leaf predicates: they group ahead of AND and OR, which is why a = 1 AND b IN (SELECT id FROM u) OR c = 2 reads as ((a = 1) AND (b IN (SELECT id FROM u))) OR (c = 2) — the same shape it would have with a literal value list. See Subqueries and derived tables.

Examples:

BETWEEN:

-- Range check
SELECT * FROM products
WHERE price BETWEEN 50 AND 100;
-- Equivalent to: price >= 50 AND price <= 100

-- With OR (lower precedence)
SELECT * FROM products
WHERE price BETWEEN 50 AND 100 OR category = 'sale';
-- Evaluated as: (price BETWEEN 50 AND 100) OR (category = 'sale')

-- NOT BETWEEN
SELECT * FROM products
WHERE price NOT BETWEEN 50 AND 100;
-- Equivalent to: price < 50 OR price > 100

IN:

-- Set membership
SELECT * FROM orders
WHERE status IN ('pending', 'processing', 'shipped');

-- With OR (lower precedence)
SELECT * FROM products
WHERE category IN ('electronics', 'computers') OR on_sale = true;
-- Evaluated as: (category IN (...)) OR (on_sale = true)

-- NOT IN
SELECT * FROM users
WHERE country NOT IN ('US', 'CA', 'MX');

LIKE:

-- Pattern matching
SELECT * FROM products
WHERE name LIKE '%phone%';

-- With OR
SELECT * FROM products
WHERE name LIKE '%phone%' OR name LIKE '%tablet%';
-- Evaluated as: (name LIKE '%phone%') OR (name LIKE '%tablet%')

-- NOT LIKE
SELECT * FROM products
WHERE name NOT LIKE '%discontinued%';

RLIKE (Regular Expression):

-- Regex pattern matching
SELECT * FROM products
WHERE name RLIKE '^[A-Z].*Pro$';

-- With OR
SELECT * FROM products
WHERE name RLIKE 'iPhone|iPad' OR category = 'Apple';
-- Evaluated as: (name RLIKE 'iPhone|iPad') OR (category = 'Apple')

Mixed with AND:

-- BETWEEN with AND (AND has lower precedence)
SELECT * FROM products
WHERE price BETWEEN 50 AND 100 AND category = 'electronics';
-- Evaluated as: (price BETWEEN 50 AND 100) AND (category = 'electronics')

8. Logical AND

Eighth precedence - Logical conjunction (both conditions must be true).

Examples:

Basic AND:

-- Both conditions must be true
SELECT * FROM users
WHERE is_active = true AND is_verified = true;

-- Multiple AND conditions
SELECT * FROM products
WHERE price > 50
  AND price < 100
  AND in_stock = true;

AND with OR (OR has lower precedence):

-- AND evaluated before OR
SELECT * FROM products
WHERE category = 'electronics' AND price < 100 OR on_sale = true;
-- Evaluated as: ((category = 'electronics') AND (price < 100)) OR (on_sale = true)
-- Matches: (electronics under $100) OR (anything on sale)

-- Use parentheses for different logic
SELECT * FROM products
WHERE category = 'electronics' AND (price < 100 OR on_sale = true);
-- Evaluated as: (category = 'electronics') AND ((price < 100) OR (on_sale = true))
-- Matches: electronics that are (under $100 OR on sale)

Complex AND Expressions:

-- Multiple AND with comparisons
SELECT * FROM orders
WHERE status = 'shipped'
  AND shipped_date >= '2025-01-01'
  AND total_amount > 100
  AND customer_id IN (SELECT id FROM premium_customers);

9. Logical OR

Lowest precedence - Logical disjunction (at least one condition must be true).

Examples:

Basic OR:

-- At least one condition must be true
SELECT * FROM users
WHERE country = 'US' OR country = 'CA';

-- Multiple OR conditions
SELECT * FROM products
WHERE category = 'electronics'
   OR category = 'computers'
   OR category = 'phones';

OR with AND (AND has higher precedence):

-- Example from specification
SELECT * FROM products
WHERE category = 'electronics' AND price < 100 OR on_sale = true;
-- Evaluated as: ((category = 'electronics') AND (price < 100)) OR (on_sale = true)

-- Explicit parentheses for clarity
SELECT * FROM products
WHERE (category = 'electronics' AND price < 100) OR on_sale = true;

-- Different logic with parentheses
SELECT * FROM products
WHERE category = 'electronics' AND (price < 100 OR on_sale = true);

BETWEEN with OR:

-- Example from specification
SELECT * FROM products
WHERE price BETWEEN 50 AND 100 OR category = 'sale';
-- Evaluated as: (price BETWEEN 50 AND 100) OR (category = 'sale')

-- Multiple BETWEEN with OR
SELECT * FROM products
WHERE price BETWEEN 10 AND 50
   OR price BETWEEN 200 AND 300
   OR category = 'clearance';

Complex OR Expressions:

-- OR with multiple conditions
SELECT * FROM orders
WHERE status = 'cancelled'
   OR status = 'refunded'
   OR (status = 'pending' AND created_date < DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY));

Practical Examples

Example 1: Arithmetic Precedence

-- Without parentheses (follows precedence)
SELECT 1 + 2 * 3 AS v;
-- Result: 7
-- Evaluation: 1 + (2 * 3) = 1 + 6 = 7

-- With parentheses (override precedence)
SELECT (1 + 2) * 3 AS v;
-- Result: 9
-- Evaluation: (1 + 2) * 3 = 3 * 3 = 9

-- Complex calculation
SELECT 10 + 5 * 2 - 8 / 4 AS result;
-- Result: 18
-- Evaluation: 10 + (5 * 2) - (8 / 4) = 10 + 10 - 2 = 18

Example 2: Comparison and Logical Operators

-- BETWEEN with OR
SELECT * FROM products
WHERE price BETWEEN 50 AND 100 OR category = 'sale';
-- Evaluated as: (price BETWEEN 50 AND 100) OR (category = 'sale')

-- AND before OR
SELECT * FROM products
WHERE category = 'electronics' AND price < 100 OR on_sale = true;
-- Evaluated as: ((category = 'electronics') AND (price < 100)) OR (on_sale = true)

-- Use parentheses for clarity
SELECT * FROM products
WHERE category = 'electronics' AND (price < 100 OR on_sale = true);
-- Evaluated as: (category = 'electronics') AND ((price < 100) OR (on_sale = true))

Example 3: NOT Operator

-- NOT with high precedence
SELECT * FROM users
WHERE NOT is_active = true AND is_verified = true;
-- Evaluated as: (NOT is_active = true) AND (is_verified = true)

-- NOT over both conditions: NOT (A AND B) is written (NOT A) OR (NOT B)
SELECT * FROM users
WHERE NOT is_active = true OR NOT is_verified = true;
-- Evaluated as: (NOT is_active = true) OR (NOT is_verified = true)

Example 4: Complex Business Logic

-- Find premium or high-value orders
SELECT * FROM orders
WHERE customer_type = 'premium'
  AND total_amount > 500
  OR total_amount > 1000
  AND status = 'completed';
-- Evaluated as:
-- ((customer_type = 'premium') AND (total_amount > 500))
-- OR
-- ((total_amount > 1000) AND (status = 'completed'))

-- More readable with parentheses
SELECT * FROM orders
WHERE (customer_type = 'premium' AND total_amount > 500)
   OR (total_amount > 1000 AND status = 'completed');

Example 5: IN with OR and AND

-- IN with AND and OR
SELECT * FROM products
WHERE category IN ('electronics', 'computers')
  AND price < 500
  OR on_sale = true;
-- Evaluated as:
-- ((category IN ('electronics', 'computers')) AND (price < 500))
-- OR
-- (on_sale = true)

-- Clearer with parentheses
SELECT * FROM products
WHERE (category IN ('electronics', 'computers') AND price < 500)
   OR on_sale = true;

Best Practices

1. Use Parentheses for Clarity

-- Good: Explicit and clear
SELECT * FROM orders
WHERE (status = 'pending' AND created_date < '2025-01-01')
   OR (priority = 'high');

-- Avoid: Relies on precedence knowledge
SELECT * FROM orders
WHERE status = 'pending' AND created_date < '2025-01-01'
   OR priority = 'high';

2. Group Related Conditions

-- Good: Logical grouping
SELECT * FROM products
WHERE (category = 'electronics' AND price < 100)
   OR (category = 'books' AND price < 20)
   OR on_sale = true;

-- Avoid: Flat structure
SELECT * FROM products
WHERE category = 'electronics' AND price < 100
   OR category = 'books' AND price < 20
   OR on_sale = true;

3. Document Complex Logic

-- Good: Commented for clarity
SELECT * FROM orders
WHERE
  -- High priority orders
  (priority = 'high' AND status = 'pending')
  -- OR old pending orders
  OR (status = 'pending' AND created_date < DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY))
  -- OR VIP customer orders
  OR customer_id IN (SELECT id FROM vip_customers);

Common Mistakes

Mistake 1: Forgetting AND before OR precedence

-- Wrong interpretation
SELECT * FROM products
WHERE category = 'electronics' AND price < 100 OR on_sale = true;
-- Might think: electronics that are (under $100 OR on sale)
-- Actually means: (electronics under $100) OR (anything on sale)

-- Correct with parentheses
SELECT * FROM products
WHERE category = 'electronics' AND (price < 100 OR on_sale = true);

Mistake 2: Arithmetic order confusion

-- Wrong
SELECT price + tax * quantity FROM orders;
-- Evaluated as: price + (tax * quantity)

-- Correct
SELECT (price + tax) * quantity FROM orders;

Mistake 3: NOT operator scope

-- Wrong interpretation
SELECT * FROM users
WHERE NOT is_active = true AND is_verified = true;
-- Might think: NOT (is_active = true AND is_verified = true)
-- Actually means: (NOT is_active = true) AND (is_verified = true)

-- If you want NOT (is_active = true AND is_verified = true), apply De Morgan's law
-- (NOT before a parenthesised group is not supported)
SELECT * FROM users
WHERE NOT is_active = true OR NOT is_verified = true;

Mistake 4: Multiple comparisons

-- Wrong (syntax error)
SELECT * FROM products
WHERE 10 < price < 100;

-- Correct
SELECT * FROM products
WHERE price > 10 AND price < 100;

-- Or use BETWEEN
SELECT * FROM products
WHERE price BETWEEN 10 AND 100;

Precedence Summary Table

Level	Operators	Associativity	Example
1	`(...)`	N/A	`(a + b) * c`
2	`-`, `+`, `NOT`	Right	`-a`, `NOT b`
3	`*`, `/`, `%`	Left	`a * b / c`
4	`+`, `-`	Left	`a + b - c`
5	`<`, `<=`, `>`, `>=`	Left	`a < b`
6	`=`, `!=`, `<>`	Left	`a = b`
7	`BETWEEN`, `IN`, `LIKE`, `RLIKE`	N/A	`a BETWEEN 1 AND 10`
7	`EXISTS`	N/A	`EXISTS (SELECT 1 FROM t)`
8	`AND`	Left	`a AND b AND c`
9	`OR`	Left	`a OR b OR c`

Associativity:

Left: Operators of same precedence evaluate left-to-right: a - b - c = (a - b) - c
Right: Operators evaluate right-to-left: -a applies to a first
N/A: Not applicable for grouping or special operators

Operator Precedence

Precedence Order

1. Parentheses (...)

2. Unary Operators: -, +, NOT

3. Multiplicative: *, /, %

4. Additive: +, -

5. Comparison: <, <=, >, >=

6. Equality: =, !=, <>

7. Membership & Pattern: BETWEEN, IN, LIKE, RLIKE

8. Logical AND

9. Logical OR

Practical Examples

Best Practices

Common Mistakes

Precedence Summary Table

1. Parentheses `(...)`

2. Unary Operators: `-`, `+`, `NOT`

3. Multiplicative: `*`, `/`, `%`

4. Additive: `+`, `-`

5. Comparison: `<`, `<=`, `>`, `>=`

6. Equality: `=`, `!=`, `<>`

7. Membership & Pattern: `BETWEEN`, `IN`, `LIKE`, `RLIKE`