Database Management SystemsSQL Queries

Logical Operators

LevelBeginner

Duration55 mins

TopicSQL Queries

4 / 5

Operator Precedence

When Order Matters

Consider this WHERE clause:

WHERE department = 'Sales' OR department = 'Marketing' AND salary > 50000

What does this query return?

Option A: Employees in Sales or Marketing, but only if they earn over $50,000
Option B: All Sales employees, OR Marketing employees who earn over $50,000

If you're unsure, you're not alone. This ambiguity is the source of countless bugs in SQL queries. The answer is Option B—but why? The reason lies in operator precedence: the rules that determine which operators are evaluated first when multiple operators appear in an expression.

What You Will Learn

By the end of this page, you will master SQL operator precedence: the hierarchy of NOT > AND > OR, how parentheses override default precedence, common bugs from precedence misunderstanding, and best practices for writing clear, unambiguous conditions that do exactly what you intend.

The Precedence Hierarchy

In SQL, when multiple logical operators appear in an expression without parentheses, they are evaluated according to a fixed precedence hierarchy. Operators with higher precedence are evaluated first.

SQL Logical Operator Precedence (Highest to Lowest):

1. NOT     (highest precedence - evaluated first)
2. AND    
3. OR      (lowest precedence - evaluated last)

Key Insight: AND binds tighter than OR. This means expressions like A OR B AND C are parsed as A OR (B AND C), not (A OR B) AND C.

SQL Operator Precedence (Complete)
Precedence	Operator Category	Examples
1 (Highest)	Parentheses	`( )`
2	Unary operators	`NOT`, `- (negative)`
3	Multiplicative	`*`, `/`, `%`
4	Additive	`+`, `-`
5	Comparison	`=`, `!=`, `<`, `>`, `<=`, `>=`, `LIKE`, `IN`, `BETWEEN`, `IS NULL`
6	NOT (logical)	`NOT` (applied to Boolean)
7	AND	`AND`
8 (Lowest)	OR	`OR`

Why This Order?

The precedence hierarchy follows mathematical and logical conventions:

NOT is a unary operator that modifies a single value, like the negative sign in math. It should bind tightly to its operand.
AND is conceptually like multiplication (intersection, both must be true). In Boolean algebra, AND is often written as multiplication: A · B.
OR is conceptually like addition (union, either can be true). In Boolean algebra, OR is often written as addition: A + B.

Just as 2 + 3 × 4 = 2 + 12 = 14 (multiplication before addition), A OR B AND C = A OR (B AND C) (AND before OR).

Memory Aid

Think of the mnemonic 'NO-A-O': NOT first, AND second, OR last. Or remember that AND is 'tighter' (more restrictive) than OR, so it binds tighter in precedence too.

Precedence in Action

Let's see how precedence affects query interpretation with concrete examples:

precedence_examples.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
-- Example 1: AND before OR
SELECT * FROM employees
WHERE department = 'Sales' OR department = 'Marketing' AND salary > 50000;
 
-- Parsed as:
WHERE department = 'Sales' OR (department = 'Marketing' AND salary > 50000)
 
-- Returns:
-- - ALL Sales employees (regardless of salary)
-- - Marketing employees earning over $50,000
 
-- If you wanted BOTH departments filtered by salary:
WHERE (department = 'Sales' OR department = 'Marketing') AND salary > 50000
 
 
-- Example 2: NOT before AND and OR
SELECT * FROM products
WHERE NOT available AND price > 100 OR category = 'Premium';
 
-- Parsed as:
WHERE ((NOT available) AND price > 100) OR (category = 'Premium')
 
-- NOT binds to 'available' only, not the entire expression
 
-- Returns:
-- - Unavailable products over $100
-- - OR any Premium products (regardless of availability or price)
 
-- More explicit version:
WHERE (NOT available AND price > 100) OR category = 'Premium'
 
 
-- Example 3: Multiple ANDs and ORs
SELECT * FROM orders
WHERE status = 'Pending' OR status = 'Processing' AND priority = 'High' AND amount > 1000;
 
-- Parsed as:
WHERE status = 'Pending' OR ((status = 'Processing') AND (priority = 'High') AND (amount > 1000))
 
-- Returns:
-- - ALL Pending orders
-- - Processing orders that are High priority AND over $1000

The OR Expansion Problem

When OR appears with AND, it often expands results more than intended. A single OR without parentheses can effectively bypass all subsequent AND conditions for that branch. This is one of the most common SQL bugs.

Parentheses Override Precedence

Parentheses have the highest precedence—they always override default operator precedence. Use them to:

Express your exact intent when it differs from default precedence
Make precedence explicit for clarity, even when not strictly necessary
Group related conditions logically for readability

parentheses_usage.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
-- Scenario: High-value orders from Sales or Marketing departments
 
-- INCORRECT (default precedence)
SELECT * FROM orders
WHERE department = 'Sales' OR department = 'Marketing'
  AND amount > 10000;
-- Gets ALL Sales orders plus Marketing orders over $10,000
 
-- CORRECT (explicit grouping)
SELECT * FROM orders
WHERE (department = 'Sales' OR department = 'Marketing')
  AND amount > 10000;
-- Gets only orders over $10,000 from Sales or Marketing
 
 
-- Complex example: Multiple OR groups with AND
SELECT * FROM employees
WHERE (department = 'Engineering' OR department = 'Research')
  AND (level >= 4 OR years_experience >= 10)
  AND performance_rating >= 4.0;
 
-- This finds: Senior engineers/researchers with good performance
-- Without parentheses, this would be parsed very differently!
 
 
-- NOT with compound expressions
SELECT * FROM products
WHERE NOT (category = 'Electronics' AND price > 500);
 
-- Without parentheses:
SELECT * FROM products
WHERE NOT category = 'Electronics' AND price > 500;
-- This means: Non-Electronics items over $500 (NOT binds only to category)
 
 
-- Nested parentheses for complex logic
SELECT * FROM tickets
WHERE ((priority = 'Critical' OR priority = 'High')
       AND age_hours > 4)
   OR (priority = 'Medium' AND age_hours > 24)
   OR (assignee IS NULL AND created_at < NOW() - INTERVAL '1 hour');

When in Doubt, Use Parentheses

Explicit parentheses are never wrong. Even if they match default precedence, they communicate intent clearly to other developers (and your future self). The minor visual overhead is worth the clarity and bug prevention.

Common Precedence Bugs

Precedence-related bugs are insidious because the query runs without errors—it just returns wrong results. Here are the most common patterns:

Common Precedence Bugs and Fixes
Bug Pattern	Intent	Problem	Fix
`A OR B AND C`	Either A or B, with condition C	Gets A unrestricted, only B restricted	`(A OR B) AND C`
`NOT A AND B`	Neither A nor B	Gets non-A with B	`NOT (A AND B)` or `NOT A AND NOT B`
`A AND B OR C AND D`	A with B, or C with D	Actually correct by precedence!	Consider parentheses for clarity
`NOT A OR B`	Neither A nor B	Gets non-A or any B	`NOT (A OR B)` or `NOT A AND NOT B`

precedence_bugs.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
-- BUG 1: The classic "OR expansion" bug
-- Intent: Active users in US or Canada
SELECT * FROM users
WHERE status = 'Active' AND country = 'US' OR country = 'Canada';
 
-- What it does: (Active AND US) OR (any Canada)
-- Includes ALL Canadian users regardless of status!
 
-- Fix:
SELECT * FROM users
WHERE status = 'Active' AND (country = 'US' OR country = 'Canada');
-- Or use IN:
SELECT * FROM users
WHERE status = 'Active' AND country IN ('US', 'Canada');
 
 
-- BUG 2: NOT scope confusion
-- Intent: Products not in Electronics or Clothing categories
SELECT * FROM products
WHERE NOT category = 'Electronics' OR category = 'Clothing';
 
-- What it does: (NOT Electronics) OR (any Clothing)
-- Includes ALL Clothing products!
 
-- Fix:
SELECT * FROM products
WHERE NOT (category = 'Electronics' OR category = 'Clothing');
-- Or:
SELECT * FROM products
WHERE category NOT IN ('Electronics', 'Clothing');
 
 
-- BUG 3: Multiple-column filter bypass
-- Intent: Shipped orders in Q1 2024
SELECT * FROM orders
WHERE status = 'Pending' OR status = 'Shipped'
  AND order_date >= '2024-01-01'
  AND order_date < '2024-04-01';
 
-- What it does: (ALL Pending) OR (Shipped in Q1)
-- Gets Pending orders from ANY date!
 
-- Fix:
SELECT * FROM orders
WHERE (status = 'Pending' OR status = 'Shipped')
  AND order_date >= '2024-01-01'
  AND order_date < '2024-04-01';
 
 
-- BUG 4: Security filter bypass (CRITICAL!)
-- Intent: Show user's own orders or orders they have access to
SELECT * FROM orders
WHERE owner_id = @current_user OR access_level = 'Public'
  AND NOT deleted;
 
-- What it does: (owner's orders regardless of deleted) OR (public non-deleted)
-- Shows user's OWN DELETED orders! Security hole!
 
-- Fix:
SELECT * FROM orders
WHERE (owner_id = @current_user OR access_level = 'Public')
  AND NOT deleted;

Security Implications

Precedence bugs in access control queries can create serious security vulnerabilities. A misplaced OR can bypass authentication checks, expose soft-deleted data, or show records from other users. Always review WHERE clauses in security-sensitive queries with explicit parentheses.

Evaluation Order and Short-Circuiting

Precedence determines how expressions are grouped. Evaluation order determines the sequence in which conditions are actually checked. These are distinct concepts.

Key Point: SQL does not guarantee left-to-right evaluation within a precedence level. Database optimizers may reorder conditions for efficiency.

evaluation_order.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
-- Precedence determines GROUPING
WHERE A OR B AND C
-- Grouped as: A OR (B AND C)
-- This is structural—it defines what the expression MEANS
 
-- Evaluation order determines EXECUTION
-- The database might evaluate:
--   - A first, then B AND C
--   - B first, then C, then A
--   - Any order the optimizer deems efficient
 
-- This matters for:
 
-- 1. Performance (optimizer reorders for speed)
WHERE expensive_function() = 'X' AND simple_column = 'Y';
-- Optimizer may check simple_column first to avoid expensive_function
-- when possible
 
-- 2. Short-circuit safety expectations
WHERE other_column IS NOT NULL 
  AND CAST(other_column AS INTEGER) > 100;
-- DON'T assume IS NOT NULL is checked first!
-- The CAST might be evaluated first, causing an error
 
-- 3. Subquery execution
WHERE status = 'Active' OR id IN (SELECT id FROM large_table);
-- Optimizer may run the subquery regardless of whether 
-- status = 'Active' is checked first
 
 
-- SAFE PATTERN: Use CASE to guarantee order
SELECT *
FROM mixed_data
WHERE CASE 
        WHEN data_type = 'numeric' THEN CAST(value AS INT) > 100
        ELSE FALSE
      END;
-- CASE branches are guaranteed to short-circuit

Precedence ≠ Evaluation Order

Precedence is syntactic (how grouping is determined). Evaluation order is semantic (how computation proceeds). The optimizer may evaluate in any order that produces the correct result for the precedence structure. Use CASE when order matters for correctness.

Formatting for Clarity

Good formatting makes precedence visually obvious, reducing bugs and improving maintainability:

basic_formatting.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
-- Poor: Everything on one line
WHERE status='Active' AND type='Premium' OR type='Enterprise' AND created>'2024-01-01'
 
-- Better: Line breaks with operators
WHERE status = 'Active'
  AND type = 'Premium'
   OR type = 'Enterprise'
  AND created > '2024-01-01'
 
-- Best: Parentheses + aligned operators + spacing
WHERE status = 'Active'
  AND (type = 'Premium' OR type = 'Enterprise')
  AND created > '2024-01-01'

Formatting Guidelines

•One condition per line for multi-condition WHEREs
•Operator at line start (AND, OR) for easy scanning
•Consistent indentation for grouped conditions
•Parentheses for all mixed AND/OR even when not strictly required
•Comments for business logic explaining why, not what

Best Practices

Avoid

•Relying on memorized precedence — Others (or you later) may not remember
•Mixing AND and OR without parentheses — Almost always a bug waiting to happen
•Long chains without grouping — Difficult to parse mentally
•Assuming left-to-right evaluation — Optimizers can reorder
•Copy-pasting conditions — Easy to forget updating grouped logic

Embrace

•Explicit parentheses always — Make intent unmistakable
•Group related conditions visually — Use line breaks and indentation
•Use IN for multiple values — Cleaner than chains of OR
•Test with edge cases — Verify query returns exactly what's expected
•Review in code review — Ask: 'Is the grouping correct?'

best_practices.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
-- PATTERN 1: Always use parentheses with mixed operators
-- Even when precedence matches intent, be explicit
 
-- Instead of:
WHERE a = 1 AND b = 2 OR c = 3 AND d = 4
 
-- Write:
WHERE (a = 1 AND b = 2) OR (c = 3 AND d = 4)
 
 
-- PATTERN 2: Use IN instead of multiple ORs on same column
-- Instead of:
WHERE status = 'A' OR status = 'B' OR status = 'C'
 
-- Write:
WHERE status IN ('A', 'B', 'C')
 
 
-- PATTERN 3: Factor out common conditions
-- Instead of:
WHERE (status = 'Active' AND type = 'A')
   OR (status = 'Active' AND type = 'B')
   OR (status = 'Active' AND type = 'C')
 
-- Write:
WHERE status = 'Active' AND type IN ('A', 'B', 'C')
 
 
-- PATTERN 4: Use CTEs or subqueries for very complex logic
-- Instead of:
WHERE (complex_condition_1 AND complex_condition_2)
   OR (complex_condition_3 AND complex_condition_4)
   OR (complex_condition_5)
 
-- Consider:
WITH filtered AS (
    SELECT *, 
           CASE WHEN complex_condition_1 AND complex_condition_2 THEN 'group1'
                WHEN complex_condition_3 AND complex_condition_4 THEN 'group2'
                WHEN complex_condition_5 THEN 'group3'
                ELSE NULL
           END AS filter_group
    FROM my_table
)
SELECT * FROM filtered WHERE filter_group IS NOT NULL;

Summary: Mastering Precedence

Operator precedence is the invisible force that shapes query behavior. Let's consolidate the key insights:

Key Takeaways

•Precedence Order: NOT > AND > OR — NOT binds tightest, OR binds loosest. AND is evaluated before OR without parentheses.
•A OR B AND C = A OR (B AND C) — This is the most common source of precedence bugs. The AND binds B and C together.
•Parentheses override everything — They have the highest precedence. Use them to express exact intent.
•Precedence ≠ Evaluation order — Precedence determines grouping; the optimizer may evaluate in any order.
•Format for clarity — Line breaks, indentation, and alignment make precedence visually obvious.
•When in doubt, parenthesize — Explicit parentheses prevent bugs and communicate intent to future readers.

What's Next:

Now that we understand each operator individually and how they interact through precedence, the final page brings everything together. We'll explore complex conditions—multi-level Boolean expressions that combine AND, OR, NOT, and parentheses to express sophisticated business logic.

Page Complete

You now understand SQL operator precedence comprehensively—the hierarchy, common bugs, parentheses usage, and formatting best practices. Next, we'll synthesize everything into complex condition patterns for real-world scenarios.

4 / 5

Loading learning content...

Database Management SystemsSQL Queries

Logical Operators

LevelBeginner

Duration55 mins

TopicSQL Queries

4 / 5

Operator Precedence

When Order Matters

Consider this WHERE clause:

WHERE department = 'Sales' OR department = 'Marketing' AND salary > 50000

What does this query return?

Option A: Employees in Sales or Marketing, but only if they earn over $50,000
Option B: All Sales employees, OR Marketing employees who earn over $50,000

What You Will Learn

The Precedence Hierarchy

SQL Logical Operator Precedence (Highest to Lowest):

1. NOT     (highest precedence - evaluated first)
2. AND    
3. OR      (lowest precedence - evaluated last)

Key Insight: AND binds tighter than OR. This means expressions like A OR B AND C are parsed as A OR (B AND C), not (A OR B) AND C.

SQL Operator Precedence (Complete)
Precedence	Operator Category	Examples
1 (Highest)	Parentheses	`( )`
2	Unary operators	`NOT`, `- (negative)`
3	Multiplicative	`*`, `/`, `%`
4	Additive	`+`, `-`
5	Comparison	`=`, `!=`, `<`, `>`, `<=`, `>=`, `LIKE`, `IN`, `BETWEEN`, `IS NULL`
6	NOT (logical)	`NOT` (applied to Boolean)
7	AND	`AND`
8 (Lowest)	OR	`OR`

Why This Order?

The precedence hierarchy follows mathematical and logical conventions:

NOT is a unary operator that modifies a single value, like the negative sign in math. It should bind tightly to its operand.
AND is conceptually like multiplication (intersection, both must be true). In Boolean algebra, AND is often written as multiplication: A · B.
OR is conceptually like addition (union, either can be true). In Boolean algebra, OR is often written as addition: A + B.

Just as 2 + 3 × 4 = 2 + 12 = 14 (multiplication before addition), A OR B AND C = A OR (B AND C) (AND before OR).

Memory Aid

Think of the mnemonic 'NO-A-O': NOT first, AND second, OR last. Or remember that AND is 'tighter' (more restrictive) than OR, so it binds tighter in precedence too.

Precedence in Action

Let's see how precedence affects query interpretation with concrete examples:

precedence_examples.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
-- Example 1: AND before OR
SELECT * FROM employees
WHERE department = 'Sales' OR department = 'Marketing' AND salary > 50000;
 
-- Parsed as:
WHERE department = 'Sales' OR (department = 'Marketing' AND salary > 50000)
 
-- Returns:
-- - ALL Sales employees (regardless of salary)
-- - Marketing employees earning over $50,000
 
-- If you wanted BOTH departments filtered by salary:
WHERE (department = 'Sales' OR department = 'Marketing') AND salary > 50000
 
 
-- Example 2: NOT before AND and OR
SELECT * FROM products
WHERE NOT available AND price > 100 OR category = 'Premium';
 
-- Parsed as:
WHERE ((NOT available) AND price > 100) OR (category = 'Premium')
 
-- NOT binds to 'available' only, not the entire expression
 
-- Returns:
-- - Unavailable products over $100
-- - OR any Premium products (regardless of availability or price)
 
-- More explicit version:
WHERE (NOT available AND price > 100) OR category = 'Premium'
 
 
-- Example 3: Multiple ANDs and ORs
SELECT * FROM orders
WHERE status = 'Pending' OR status = 'Processing' AND priority = 'High' AND amount > 1000;
 
-- Parsed as:
WHERE status = 'Pending' OR ((status = 'Processing') AND (priority = 'High') AND (amount > 1000))
 
-- Returns:
-- - ALL Pending orders
-- - Processing orders that are High priority AND over $1000

The OR Expansion Problem

Parentheses Override Precedence

Parentheses have the highest precedence—they always override default operator precedence. Use them to:

Express your exact intent when it differs from default precedence
Make precedence explicit for clarity, even when not strictly necessary
Group related conditions logically for readability

parentheses_usage.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
-- Scenario: High-value orders from Sales or Marketing departments
 
-- INCORRECT (default precedence)
SELECT * FROM orders
WHERE department = 'Sales' OR department = 'Marketing'
  AND amount > 10000;
-- Gets ALL Sales orders plus Marketing orders over $10,000
 
-- CORRECT (explicit grouping)
SELECT * FROM orders
WHERE (department = 'Sales' OR department = 'Marketing')
  AND amount > 10000;
-- Gets only orders over $10,000 from Sales or Marketing
 
 
-- Complex example: Multiple OR groups with AND
SELECT * FROM employees
WHERE (department = 'Engineering' OR department = 'Research')
  AND (level >= 4 OR years_experience >= 10)
  AND performance_rating >= 4.0;
 
-- This finds: Senior engineers/researchers with good performance
-- Without parentheses, this would be parsed very differently!
 
 
-- NOT with compound expressions
SELECT * FROM products
WHERE NOT (category = 'Electronics' AND price > 500);
 
-- Without parentheses:
SELECT * FROM products
WHERE NOT category = 'Electronics' AND price > 500;
-- This means: Non-Electronics items over $500 (NOT binds only to category)
 
 
-- Nested parentheses for complex logic
SELECT * FROM tickets
WHERE ((priority = 'Critical' OR priority = 'High')
       AND age_hours > 4)
   OR (priority = 'Medium' AND age_hours > 24)
   OR (assignee IS NULL AND created_at < NOW() - INTERVAL '1 hour');

When in Doubt, Use Parentheses

Common Precedence Bugs

Precedence-related bugs are insidious because the query runs without errors—it just returns wrong results. Here are the most common patterns:

Common Precedence Bugs and Fixes
Bug Pattern	Intent	Problem	Fix
`A OR B AND C`	Either A or B, with condition C	Gets A unrestricted, only B restricted	`(A OR B) AND C`
`NOT A AND B`	Neither A nor B	Gets non-A with B	`NOT (A AND B)` or `NOT A AND NOT B`
`A AND B OR C AND D`	A with B, or C with D	Actually correct by precedence!	Consider parentheses for clarity
`NOT A OR B`	Neither A nor B	Gets non-A or any B	`NOT (A OR B)` or `NOT A AND NOT B`

precedence_bugs.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
-- BUG 1: The classic "OR expansion" bug
-- Intent: Active users in US or Canada
SELECT * FROM users
WHERE status = 'Active' AND country = 'US' OR country = 'Canada';
 
-- What it does: (Active AND US) OR (any Canada)
-- Includes ALL Canadian users regardless of status!
 
-- Fix:
SELECT * FROM users
WHERE status = 'Active' AND (country = 'US' OR country = 'Canada');
-- Or use IN:
SELECT * FROM users
WHERE status = 'Active' AND country IN ('US', 'Canada');
 
 
-- BUG 2: NOT scope confusion
-- Intent: Products not in Electronics or Clothing categories
SELECT * FROM products
WHERE NOT category = 'Electronics' OR category = 'Clothing';
 
-- What it does: (NOT Electronics) OR (any Clothing)
-- Includes ALL Clothing products!
 
-- Fix:
SELECT * FROM products
WHERE NOT (category = 'Electronics' OR category = 'Clothing');
-- Or:
SELECT * FROM products
WHERE category NOT IN ('Electronics', 'Clothing');
 
 
-- BUG 3: Multiple-column filter bypass
-- Intent: Shipped orders in Q1 2024
SELECT * FROM orders
WHERE status = 'Pending' OR status = 'Shipped'
  AND order_date >= '2024-01-01'
  AND order_date < '2024-04-01';
 
-- What it does: (ALL Pending) OR (Shipped in Q1)
-- Gets Pending orders from ANY date!
 
-- Fix:
SELECT * FROM orders
WHERE (status = 'Pending' OR status = 'Shipped')
  AND order_date >= '2024-01-01'
  AND order_date < '2024-04-01';
 
 
-- BUG 4: Security filter bypass (CRITICAL!)
-- Intent: Show user's own orders or orders they have access to
SELECT * FROM orders
WHERE owner_id = @current_user OR access_level = 'Public'
  AND NOT deleted;
 
-- What it does: (owner's orders regardless of deleted) OR (public non-deleted)
-- Shows user's OWN DELETED orders! Security hole!
 
-- Fix:
SELECT * FROM orders
WHERE (owner_id = @current_user OR access_level = 'Public')
  AND NOT deleted;

Security Implications

Evaluation Order and Short-Circuiting

Precedence determines how expressions are grouped. Evaluation order determines the sequence in which conditions are actually checked. These are distinct concepts.

Key Point: SQL does not guarantee left-to-right evaluation within a precedence level. Database optimizers may reorder conditions for efficiency.

evaluation_order.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
-- Precedence determines GROUPING
WHERE A OR B AND C
-- Grouped as: A OR (B AND C)
-- This is structural—it defines what the expression MEANS
 
-- Evaluation order determines EXECUTION
-- The database might evaluate:
--   - A first, then B AND C
--   - B first, then C, then A
--   - Any order the optimizer deems efficient
 
-- This matters for:
 
-- 1. Performance (optimizer reorders for speed)
WHERE expensive_function() = 'X' AND simple_column = 'Y';
-- Optimizer may check simple_column first to avoid expensive_function
-- when possible
 
-- 2. Short-circuit safety expectations
WHERE other_column IS NOT NULL 
  AND CAST(other_column AS INTEGER) > 100;
-- DON'T assume IS NOT NULL is checked first!
-- The CAST might be evaluated first, causing an error
 
-- 3. Subquery execution
WHERE status = 'Active' OR id IN (SELECT id FROM large_table);
-- Optimizer may run the subquery regardless of whether 
-- status = 'Active' is checked first
 
 
-- SAFE PATTERN: Use CASE to guarantee order
SELECT *
FROM mixed_data
WHERE CASE 
        WHEN data_type = 'numeric' THEN CAST(value AS INT) > 100
        ELSE FALSE
      END;
-- CASE branches are guaranteed to short-circuit

Precedence ≠ Evaluation Order

Formatting for Clarity

Good formatting makes precedence visually obvious, reducing bugs and improving maintainability:

basic_formatting.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
-- Poor: Everything on one line
WHERE status='Active' AND type='Premium' OR type='Enterprise' AND created>'2024-01-01'
 
-- Better: Line breaks with operators
WHERE status = 'Active'
  AND type = 'Premium'
   OR type = 'Enterprise'
  AND created > '2024-01-01'
 
-- Best: Parentheses + aligned operators + spacing
WHERE status = 'Active'
  AND (type = 'Premium' OR type = 'Enterprise')
  AND created > '2024-01-01'

Formatting Guidelines

•One condition per line for multi-condition WHEREs
•Operator at line start (AND, OR) for easy scanning
•Consistent indentation for grouped conditions
•Parentheses for all mixed AND/OR even when not strictly required
•Comments for business logic explaining why, not what

Best Practices

Avoid

•Relying on memorized precedence — Others (or you later) may not remember
•Mixing AND and OR without parentheses — Almost always a bug waiting to happen
•Long chains without grouping — Difficult to parse mentally
•Assuming left-to-right evaluation — Optimizers can reorder
•Copy-pasting conditions — Easy to forget updating grouped logic

Embrace

•Explicit parentheses always — Make intent unmistakable
•Group related conditions visually — Use line breaks and indentation
•Use IN for multiple values — Cleaner than chains of OR
•Test with edge cases — Verify query returns exactly what's expected
•Review in code review — Ask: 'Is the grouping correct?'

best_practices.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
-- PATTERN 1: Always use parentheses with mixed operators
-- Even when precedence matches intent, be explicit
 
-- Instead of:
WHERE a = 1 AND b = 2 OR c = 3 AND d = 4
 
-- Write:
WHERE (a = 1 AND b = 2) OR (c = 3 AND d = 4)
 
 
-- PATTERN 2: Use IN instead of multiple ORs on same column
-- Instead of:
WHERE status = 'A' OR status = 'B' OR status = 'C'
 
-- Write:
WHERE status IN ('A', 'B', 'C')
 
 
-- PATTERN 3: Factor out common conditions
-- Instead of:
WHERE (status = 'Active' AND type = 'A')
   OR (status = 'Active' AND type = 'B')
   OR (status = 'Active' AND type = 'C')
 
-- Write:
WHERE status = 'Active' AND type IN ('A', 'B', 'C')
 
 
-- PATTERN 4: Use CTEs or subqueries for very complex logic
-- Instead of:
WHERE (complex_condition_1 AND complex_condition_2)
   OR (complex_condition_3 AND complex_condition_4)
   OR (complex_condition_5)
 
-- Consider:
WITH filtered AS (
    SELECT *, 
           CASE WHEN complex_condition_1 AND complex_condition_2 THEN 'group1'
                WHEN complex_condition_3 AND complex_condition_4 THEN 'group2'
                WHEN complex_condition_5 THEN 'group3'
                ELSE NULL
           END AS filter_group
    FROM my_table
)
SELECT * FROM filtered WHERE filter_group IS NOT NULL;

Summary: Mastering Precedence

Operator precedence is the invisible force that shapes query behavior. Let's consolidate the key insights:

Key Takeaways

•Precedence Order: NOT > AND > OR — NOT binds tightest, OR binds loosest. AND is evaluated before OR without parentheses.
•A OR B AND C = A OR (B AND C) — This is the most common source of precedence bugs. The AND binds B and C together.
•Parentheses override everything — They have the highest precedence. Use them to express exact intent.
•Precedence ≠ Evaluation order — Precedence determines grouping; the optimizer may evaluate in any order.
•Format for clarity — Line breaks, indentation, and alignment make precedence visually obvious.
•When in doubt, parenthesize — Explicit parentheses prevent bugs and communicate intent to future readers.

What's Next:

Page Complete

4 / 5

Logical Operators - Page 4 of 5 | OneNoughtOne