Consider a table ‘transactions’ with a composite index on (status, created_at). You execute the following query: SELECT id, amount FROM transactions WHERE status = ‘COMPLETED’ AND created_at > ‘2023-01-01’ OR status = ‘PENDING’; Given the operator precedence in SQL, how will the database engine likely process this filter, and what is the impact on index utilization?

Question

SQL Data Analyst — Hard

Consider a table ‘transactions’ with a composite index on (status, created_at). You execute the following query: SELECT id, amount FROM transactions WHERE status = ‘COMPLETED’ AND created_at > ‘2023-01-01’ OR status = ‘PENDING’; Given the operator precedence in SQL, how will the database engine likely process this filter, and what is the impact on index utilization?

Accepted Answer

In SQL, the AND operator has higher precedence than the OR operator. Consequently, the database evaluates the condition as (status = 'COMPLETED' AND created_at > '2023-01-01') OR (status = 'PENDING'). Because the OR condition splits the logic into two distinct sets, the optimizer cannot perform a single contiguous range seek on the composite index (status, created_at). It must either perform two separate index seeks and union the results or fall back to an index scan, significantly reducing performance compared to a single range condition.