Operator Precedence in SQL
Introduction
Operator precedence defines the order in which SQL evaluates operators inside an expression. When a condition contains arithmetic, comparisons, NOT, AND, and OR, the database does not simply read the expression from left to right. It applies a precedence hierarchy to build the logical meaning before it evaluates rows.
WHERE status = 'ACTIVE'
AND department_id = 10
OR department_id = 20;
Because AND normally has higher precedence than OR, SQL interprets this as (status = 'ACTIVE' AND department_id = 10) OR department_id = 20. That is different from requiring an active status for both departments. Missing parentheses can therefore change search results, bypass tenant or authorization filters, update unintended rows, and expose data. The most important practical rule is simple: when logic mixes AND and OR, use parentheses to show the intended business rule.
What Is Operator Precedence?
Operator precedence answers the question, "Which operation is evaluated first?" Consider a basic arithmetic expression:
SELECT 10 + 5 * 2 AS result;
Multiplication has higher precedence than addition, so SQL calculates 5 * 2 first and then adds 10. The result is 20, not 30. Parentheses override the default:
SELECT (10 + 5) * 2 AS result; -- 30
Precedence removes ambiguity from expressions, while parentheses communicate deliberate grouping. The database can parse an unparenthesized expression correctly according to its rules, but another developer may misunderstand the business intent. Correct syntax and clear intent are separate concerns.
A Practical Precedence Order
Exact details vary among database systems, especially for string concatenation, bitwise operations, regular expressions, JSON operators, and assignment. A useful general order from highest to lowest is:
- Parenthesized expressions
- Unary arithmetic operators such as positive and negative signs
- Multiplication, division, and modulo
- Addition and subtraction
- Comparison and predicate operators
- NOT
- AND
- OR
This hierarchy is a learning model rather than a substitute for product documentation. Within the logical operators, the sequence to remember is NOT, then AND, then OR. Parentheses should still be used whenever the intended grouping is important.
AND Has Higher Precedence Than OR
The most common precedence mistake occurs when AND and OR appear in one condition. SQL groups the AND expression before the OR expression.
WHERE a = 1
OR b = 2
AND c = 3
The effective grouping is:
WHERE a = 1
OR (b = 2 AND c = 3)
It is not (a = 1 OR b = 2) AND c = 3. Both are valid SQL, but they describe different populations. The first returns every row where a = 1, regardless of c, plus rows where both the second and third conditions hold. The second requires c = 3 for both alternatives.
Example Data and Different Results
Suppose an employee table contains active and inactive workers from departments 10 and 20. The following query returns all active employees in department 10 and every employee in department 20, including inactive employees:
SELECT employee_id, status, department_id
FROM employee
WHERE status = 'ACTIVE'
AND department_id = 10
OR department_id = 20;
If the requirement is "active employees in either department 10 or 20," the correct expression groups the department choices:
SELECT employee_id, status, department_id
FROM employee
WHERE status = 'ACTIVE'
AND (department_id = 10 OR department_id = 20);
The same-column alternatives can also be written more clearly with IN:
WHERE status = 'ACTIVE'
AND department_id IN (10, 20)
Parentheses Override Precedence
Parentheses are the strongest clarity tool in a complex condition. They override default precedence and make the intended logical units visible. They are not merely a workaround for people who cannot remember the hierarchy. They document which alternatives belong together and which conditions are mandatory.
WHERE mandatory_scope
AND (allowed_path_one OR allowed_path_two)
AND current_record
This shape is easy to review: scope and current-state rules apply to either allowed path. Even when the unparenthesized expression would parse the same way, grouping mixed logic makes later maintenance safer. A future condition can be added to the intended group rather than accidentally changing the meaning of the entire predicate.
Security Bugs from Missing Parentheses
Precedence errors become security defects when a mandatory access rule is attached to only one OR branch. Consider a search that should return visible documents belonging to the current tenant when either the title or body matches a term:
WHERE tenant_id = :tenant_id
AND title LIKE :term
OR body LIKE :term
SQL interprets the tenant rule as part of the title branch. A body match from another tenant can pass through the OR branch. The secure form groups the search alternatives:
WHERE tenant_id = :tenant_id
AND (title LIKE :term OR body LIKE :term)
Tenant, owner, organization, region, visibility, permission, and policy predicates should remain outside grouped alternatives unless the requirement explicitly assigns different scope to each branch. Parentheses do not create authorization, but they prevent an existing authorization condition from being applied to only part of the expression.
Soft Delete and Lifecycle Filters
A similar bug appears with soft-deleted or inactive records:
WHERE deleted_at IS NULL
AND email = :value
OR phone = :value
An archived row with a matching phone number can be returned because the soft-delete predicate belongs only to the email branch. Group the alternatives so the lifecycle condition applies to both:
WHERE deleted_at IS NULL
AND (email = :value OR phone = :value)
The same review applies to status = 'ACTIVE', validity dates, approval state, and environment flags. A useful code-review question is: "Which conditions must be true for every OR branch?" Those conditions usually belong outside the parenthesized group.
NOT Has Higher Precedence Than AND and OR
NOT applies to the expression immediately following it before AND and OR combine larger conditions.
WHERE NOT is_blocked
AND is_active
This means (NOT is_blocked) AND is_active, not NOT (is_blocked AND is_active). To negate an entire group, write the group explicitly:
WHERE NOT (is_blocked AND is_active)
These expressions differ for blocked inactive users. A compact NOT expression can be mathematically correct yet difficult to review, especially when NULL is possible. Positive allowlist conditions such as status IN ('READY', 'APPROVED') are often easier to understand than deeply nested negative logic.
Comparison Before Logical Combination
Comparison predicates are formed before AND and OR combine them. In the condition below, SQL first evaluates each comparison and then combines the resulting truth values:
WHERE quantity > 0
AND unit_price <= 100
OR is_featured = TRUE
The default grouping is (quantity > 0 AND unit_price <= 100) OR is_featured = TRUE. If featured items must also be in stock, the intended grouping is probably quantity > 0 AND (unit_price <= 100 OR is_featured = TRUE). Precedence resolves syntax, but only the requirement can determine the correct grouping.
Arithmetic Before Comparison
Arithmetic expressions are calculated before their result is compared. Multiplication, division, and modulo generally precede addition and subtraction:
WHERE quantity * unit_price - discount > 500
The multiplication occurs before subtraction, and the resulting amount is then compared with 500. Parentheses should still show a business formula clearly:
WHERE (quantity * unit_price) - discount > 500
If the discount is a percentage, the formula changes and should be explicit:
WHERE (quantity * unit_price) * (1 - discount_rate) > 500
Financial expressions also need deliberate data types and rounding. Parentheses clarify operation order but do not correct integer division, floating-point approximation, currency conversion, or inappropriate rounding rules.
Associativity and Same-Precedence Operators
Precedence decides between operators at different levels. Associativity decides grouping when operators share a level. Subtraction and division are commonly evaluated left to right:
SELECT 100 / 10 * 2; -- commonly (100 / 10) * 2
SELECT 20 - 5 - 3; -- commonly (20 - 5) - 3
Do not rely on readers to remember associativity in a sensitive formula. Write 100 / (10 * 2) or (100 / 10) * 2 according to intent. Parentheses are especially valuable when integer and decimal types can produce different intermediate values.
BETWEEN, IN, LIKE, and IS NULL
Compound predicates produce one logical result before they are combined with AND or OR. The AND inside BETWEEN is part of the BETWEEN syntax; it is not the same as a separate logical AND.
WHERE created_at BETWEEN :start_date AND :end_date
AND tenant_id = :tenant_id
When BETWEEN, IN, LIKE, or IS NULL participates in an OR expression, group the alternatives around the shared mandatory filter:
WHERE tenant_id = :tenant_id
AND (
status IN ('OPEN', 'PENDING')
OR assigned_to IS NULL
)
WHERE tenant_id = :tenant_id
AND (name LIKE :term OR description LIKE :term)
Writing the group vertically makes each alternative and each mandatory condition visible. It also reduces the chance that a later edit places a scope rule inside only one branch.
EXISTS, ANY, ALL, and Negative Predicates
EXISTS, quantified comparisons, and negative predicates also combine according to logical precedence:
WHERE is_active = TRUE
AND EXISTS (
SELECT 1
FROM permission p
WHERE p.user_id = u.user_id
)
OR is_admin = TRUE
The administrator branch is independent of is_active. That may be correct, or it may be an accidental bypass. Parentheses make the choice explicit. Forms such as NOT EXISTS, NOT IN, NOT BETWEEN, and NOT LIKE should be read as complete predicates. Because NULL can make NOT IN surprising, use NOT EXISTS when it more directly expresses "no matching row exists."
Three-Valued Logic Still Applies
Operator precedence determines grouping; SQL three-valued logic determines how TRUE, FALSE, and UNKNOWN combine. A comparison with NULL usually produces UNKNOWN. In a WHERE clause, only TRUE passes the filter.
| Expression | Result |
|---|---|
| TRUE AND UNKNOWN | UNKNOWN |
| FALSE AND UNKNOWN | FALSE |
| TRUE OR UNKNOWN | TRUE |
| FALSE OR UNKNOWN | UNKNOWN |
| NOT UNKNOWN | UNKNOWN |
Do not reason about UNKNOWN as though it were always FALSE. In NOT (a = 1), a NULL value for a remains UNKNOWN rather than becoming TRUE. When NULL has business meaning, state the policy explicitly with IS NULL, IS NOT NULL, or a carefully chosen normalization rule.
De Morgan's Law and Positive Logic
De Morgan's transformations are useful when reviewing negated groups:
NOT (A OR B) is equivalent to (NOT A) AND (NOT B)
NOT (A AND B) is equivalent to (NOT A) OR (NOT B)
Under SQL's UNKNOWN value, reason through the complete truth table before rewriting a nullable expression. Even where the transformation is logically valid, positive allowlist logic is often clearer. For example, status IN ('READY', 'APPROVED') is usually easier to review than excluding several disallowed states with chained NOT conditions.
DBMS-Specific Operators
String concatenation, bitwise operators, regular-expression predicates, JSON operators, and assignment syntax differ among MySQL, PostgreSQL, SQL Server, Oracle, SQLite, and other systems. The same symbol may have a different meaning or precedence, and implicit type conversion can affect the chosen operation.
For portable SQL, parenthesize expressions that mix vendor-specific operators with arithmetic or logical conditions. Test on every supported database and verify the exact product version. A query accepted by two systems is not necessarily semantically identical if collation, boolean types, concatenation, or conversion rules differ.
Complex Search Logic
A realistic search may require tenant scope, active records, a text match, and an optional ownership rule:
WHERE d.tenant_id = :tenant_id
AND d.deleted_at IS NULL
AND (
d.title LIKE :term
OR d.summary LIKE :term
OR d.body LIKE :term
)
AND (
d.visibility = 'PUBLIC'
OR d.owner_id = :user_id
)
The layout forms a visible logic tree. Each parenthesized OR group represents one business decision, while the AND lines represent requirements that every result must satisfy. Formatting is not executable logic, but it makes the executable logic easier to audit.
UPDATE and DELETE Risks
A precedence mistake in SELECT returns incorrect rows. The same mistake in UPDATE or DELETE changes data. The following statement may update all escalated rows, including those from other tenants:
UPDATE ticket
SET status = 'CLOSED'
WHERE tenant_id = :tenant_id
AND status = 'RESOLVED'
OR priority = 'ESCALATED';
If tenant scope is mandatory, group the state alternatives:
UPDATE ticket
SET status = 'CLOSED'
WHERE tenant_id = :tenant_id
AND (status = 'RESOLVED' OR priority = 'ESCALATED');
Before running a data-changing statement, execute a SELECT with the identical WHERE clause, inspect representative keys, compare the count with an expected range, and use a transaction where supported. After execution, validate the affected-row count. Parentheses are essential, but operational safeguards provide another layer of protection.
JOIN ON Conditions
Operator precedence applies inside JOIN conditions as well as WHERE clauses:
JOIN account a
ON a.tenant_id = o.tenant_id
AND a.account_id = o.account_id
OR a.legacy_code = o.account_code
The legacy-code branch is not restricted by tenant. It can create cross-tenant matches and multiply rows. If either identifier may establish the account relationship within the same tenant, write:
JOIN account a
ON a.tenant_id = o.tenant_id
AND (
a.account_id = o.account_id
OR a.legacy_code = o.account_code
)
Review OR inside a JOIN carefully. Besides correctness and security, a broad branch can cause a large intermediate result, duplicate output, expensive sorts, and misleading aggregate totals.
HAVING, CASE, and Constraints
Precedence applies wherever expressions appear, including HAVING, CASE WHEN, CHECK constraints, SELECT calculations, GROUP BY, and ORDER BY. A CHECK constraint such as type = 'A' AND value > 0 OR type = 'B' allows every type B row regardless of value. If both types require a positive value, group the type alternatives.
CHECK (
type IN ('A', 'B')
AND value > 0
)
CASE branch order is a separate concept from operator precedence. SQL evaluates the expression inside each WHEN according to precedence, while the CASE chooses the first matching WHEN in written order. Put specific conditions before broad conditions and parenthesize mixed logic within each branch.
Precedence Is Not Execution Order
Operator precedence defines how SQL parses the logical expression. It does not guarantee the physical order in which the database executes predicates. The optimizer may reorder, combine, transform, or eliminate expressions while preserving semantics. Do not depend on the left condition of AND running first to prevent an error.
WHERE denominator <> 0
AND numerator / denominator > 10
Some systems may still evaluate or transform the division in an unexpected way. Use a safe expression supported by the database, such as numerator / NULLIF(denominator, 0), or restructure the query. Likewise, predicate order should be chosen for readability, not as a performance hint. Use actual execution plans, statistics, indexes, and measured workloads for tuning.
Workflow Rules and State Transitions
Workflow predicates frequently combine several states with one mandatory ownership, date, or approval condition. Suppose a task may advance when it is READY or when it is RETRYABLE and its retry count remains below three:
WHERE status = 'READY'
OR status = 'RETRYABLE'
AND retry_count < 3
The default interpretation is status = 'READY' OR (status = 'RETRYABLE' AND retry_count < 3). The retry limit applies only to RETRYABLE tasks. If the business instead requires every advancing task to remain below the limit, group the status choices:
WHERE status IN ('READY', 'RETRYABLE')
AND retry_count < 3
Now add tenant scope, a lease expiry, and an enabled queue. The safest representation places universal requirements on separate AND lines and groups only true alternatives:
WHERE tenant_id = :tenant_id
AND queue_enabled = TRUE
AND lease_expires_at <= CURRENT_TIMESTAMP
AND status IN ('READY', 'RETRYABLE')
AND retry_count < 3
This layout also improves operational review. A developer can see that no status branch bypasses tenant ownership, queue state, lease expiry, or retry policy. When a workflow changes, tests should cover every prior and new state rather than merely the happy path.
Operator Precedence Across SQL Clauses
The same expression rules apply outside WHERE. In SELECT, arithmetic grouping changes displayed totals and derived metrics. In GROUP BY, it changes the grouping key. In ORDER BY, it changes the value used to rank rows. In HAVING, it changes which aggregate groups survive. Consistent parentheses across these clauses prevent a report from grouping by one formula while displaying or ordering by another.
SELECT product_id,
(quantity * unit_price) - discount AS net_amount
FROM order_line
ORDER BY ((quantity * unit_price) - discount) DESC;
Aliases can reduce repeated formulas when the database permits them in ORDER BY, but they do not replace deliberate grouping in the original expression. In a CASE result, arithmetic precedence still applies:
CASE
WHEN customer_type = 'PREMIUM'
THEN subtotal * (1 - premium_discount)
ELSE subtotal - standard_discount
END AS final_amount
Review each branch separately. A percentage reduction and a fixed reduction are different formulas, even if both use a minus sign. Explicit grouping makes that distinction visible to reviewers and future maintainers.
Reading a Complex Predicate as a Logic Tree
A reliable way to understand an unfamiliar predicate is to convert it into a logic tree. Begin with the lowest-precedence operator at the top level. For A AND B OR C AND D, AND forms two child groups before OR joins them, so the tree is (A AND B) OR (C AND D). For A AND (B OR C) AND D, the top level is a chain of three requirements, and the middle requirement contains two alternatives.
Give each leaf a business name instead of reading only column syntax. For example, rename A as "same tenant," B as "public document," C as "owned document," and D as "not deleted." This often exposes a flaw immediately: if the effective tree is (same tenant AND public) OR (owned AND not deleted), then neither tenant scope nor deletion state protects every route. The intended tree may be same tenant AND not deleted AND (public OR owned).
This technique is valuable in code review because it separates SQL grammar from policy. Once the policy tree is agreed upon, the SQL can be formatted to mirror it. Comments should explain unusual business reasons, but grouping and indentation should make the structure understandable without relying on comments.
Refactoring Long Boolean Expressions
Parentheses improve a moderately complex predicate, but a very long condition may need structural refactoring. Repeated same-column equality tests can become IN. Repeated existence checks may belong in a named common table expression. A derived flag can be calculated once in a CTE and then used in a simpler outer filter. The goal is not to hide logic but to give each business concept a clear name and boundary.
WITH eligible_document AS (
SELECT d.*,
CASE
WHEN d.visibility = 'PUBLIC' OR d.owner_id = :user_id
THEN 1
ELSE 0
END AS is_visible_to_user
FROM document d
WHERE d.tenant_id = :tenant_id
AND d.deleted_at IS NULL
)
SELECT *
FROM eligible_document
WHERE is_visible_to_user = 1
AND (title LIKE :term OR body LIKE :term);
This refactoring gives visibility, lifecycle, and search matching distinct roles. It must still be reviewed for optimizer behavior and correctness, and the authorization boundary should remain enforced at every required layer. Avoid replacing a clear predicate with many abstractions merely to reduce line count; refactor when naming the concepts makes verification easier.
Debugging an Unexpected Result Set
When a query returns unexpected rows, first reproduce one row that should have failed and annotate every atomic predicate as TRUE, FALSE, or UNKNOWN. Then apply the documented precedence and parentheses manually. This reveals whether the database result follows the written expression and whether the written expression follows the requirement.
Next, compare the current expression with the alternative grouping. For a mixed AND/OR filter, run diagnostic SELECT columns that expose each boolean component where the database supports boolean projections, or use CASE expressions to label them. Do not start by changing indexes or rearranging lines: whitespace does not alter grouping, and performance tuning cannot correct the wrong logical tree.
SELECT record_id,
CASE WHEN tenant_id = :tenant_id THEN 'T' ELSE 'F' END AS tenant_ok,
CASE WHEN title LIKE :term THEN 'T' ELSE 'F' END AS title_ok,
CASE WHEN body LIKE :term THEN 'T' ELSE 'F' END AS body_ok
FROM document
WHERE record_id = :unexpected_id;
Finally, turn the failing example into a regression test. Include the row that exposed the bug and a nearby row that must still pass. A good precedence test proves both exclusion and inclusion so an overcorrection does not silently remove valid records.
Performance Without Changing Meaning
Parentheses used for logical grouping do not normally impose a performance penalty. They define the expression that must be preserved; the optimizer remains free to choose an efficient equivalent plan. Removing parentheses to make SQL appear simpler can change semantics and is not a tuning technique.
After correctness is established, inspect whether OR conditions prevent efficient index access, produce inaccurate cardinality estimates, or expand joins. Depending on the database and data distribution, an OR expression may benefit from a composite index, separate indexes, an IN rewrite, or a UNION-based formulation. Every rewrite must be tested for duplicates, NULL behavior, security scope, and equivalent results before performance is compared.
Use actual execution plans and production-like values. Parameter selectivity can cause one branch to dominate for some inputs and another branch for others. Measure elapsed time, logical reads, CPU, temporary storage, row estimates, and returned counts. The fastest candidate is useful only if its truth set is identical to the verified business predicate.
Testing Operator Precedence
Tests must distinguish the plausible groupings. If every fixture satisfies both versions, the test cannot detect a missing parenthesis. For A AND (B OR C), include rows for every combination of A, B, and C, especially a row where A is false and C is true. That row passes (A AND B) OR C but must fail the intended expression.
Security tests should include a matching row from another tenant, an unauthorized owner, an archived record matching the final OR branch, and a privileged-looking value without the required scope. UPDATE and DELETE tests should assert exact identifiers and affected-row counts. JOIN tests should include equal legacy codes across tenants. NULL tests should cover NULL in each participating expression and verify UNKNOWN behavior.
Common Mistakes
- Assuming SQL evaluates every expression strictly from left to right.
- Forgetting that AND has higher precedence than OR.
- Attaching tenant, authorization, or soft-delete scope to only one OR branch.
- Using NOT without making its intended scope visible.
- Confusing precedence with short-circuit or physical execution order.
- Applying a percentage, discount, or tax formula without explicit arithmetic grouping.
- Using OR in JOIN ON without grouping shared relationship keys.
- Ignoring UNKNOWN when nullable values participate in the expression.
- Assuming vendor-specific operators have identical precedence across databases.
- Running an UPDATE or DELETE before previewing the exact target set.
Practical Review Checklist
- Identify every place where AND and OR appear together.
- Translate the predicate into a plain-language business rule.
- Group alternatives and keep mandatory scope outside the group.
- Confirm the scope of each NOT operator.
- Parenthesize financial and business calculations.
- Review NULL and UNKNOWN behavior explicitly.
- Check JOIN ON, HAVING, CASE, and constraints, not only WHERE.
- Verify DBMS-specific operator rules in official product documentation.
- Test rows that distinguish each possible grouping.
- Preview and count rows before any UPDATE or DELETE.
Interview Questions and Answers
What is operator precedence in SQL? It is the hierarchy SQL uses to group and evaluate operators in an expression.
Which has higher precedence, AND or OR? AND normally has higher precedence than OR.
Which logical operator has the highest precedence? NOT normally precedes AND, which precedes OR.
How do you override default precedence? Use parentheses around the expression that must be evaluated as one unit.
Why can missing parentheses become a security bug? A tenant or authorization condition may apply to only one OR branch, allowing another branch to bypass it.
Does predicate order guarantee execution order? No. The optimizer may transform or reorder operations while preserving logical semantics.
Does AND guarantee left-to-right short-circuit evaluation? No. Write error-safe expressions instead of depending on evaluation order.
Does precedence apply in JOIN ON and CHECK constraints? Yes. It applies wherever SQL expressions combine operators.
What is the safest style for complex conditions? Group business alternatives explicitly, format them vertically, and test every branch, NULL case, and mandatory scope condition.
Key Takeaway
SQL operator precedence determines how an expression is grouped before it is evaluated. Arithmetic operations precede comparisons, and the core logical order is NOT, AND, then OR. Parentheses override that order and, more importantly, document the intended business rule.
SAFE SQL LOGIC = EXPLICIT GROUPING
+ MANDATORY SECURITY SCOPE
+ NULL-AWARE REASONING
+ ERROR-SAFE EXPRESSIONS
+ BRANCH-SPECIFIC TESTS
+ PREVIEWED DATA CHANGES
Memorize the basic hierarchy, but do not make readers reconstruct important logic from memory. Parenthesize mixed AND and OR conditions, protect every alternative with the required scope, and verify the result with data that exposes the difference between possible groupings.