Is the SQL WHERE clause short-circuit evaluated?

sql

query-optimization

best-practices

short-circuiting

byNikita Barsukov·Aug 13, 2024

No, the SQL WHERE clause does not guarantee short-circuiting. The order of expression evaluation in SQL is subject to the database's query optimizer, making the behavior unpredictable. For example:

SELECT * FROM my_table WHERE FALSE AND costly_function() = 1;

Despite the FALSE condition appearing first, costly_function() could still be executed due to SQL's preference for efficiency over standard short-circuit logic. To protect your queries, design them without relying on short-circuit behavior.

The ins and outs of SQL short-circuiting

Optimizer’s verdict

SQL optimizers focus on crafting the most efficient plan for query execution, which might involve reshuffling WHERE clause conditions.
It's key to understand - strategies can be distinct among various databases and even between queries in the same database.

Presumption of precedence

Precedence in SQL expressions isn't fixed to be left to right; parentheses and formatting don't control this.
DBMSs are at liberty to determine the evaluation sequence, possibly leveraging indexes or other performance enhancements.

Advantages of `CASE`

The CASE expression allows you to define an assured sequence for evaluation, when you absolutely need short-circuit logic.
With CASE, there's a semblance of order in the chaos - it ensures conditions are examined in the order you specified.

Cross-database compatibility

Writing agnostic queries that don't rely on potential short-circuiting yields more consistent behavior across multiple databases.
Knowing that databases have different optimization strategies is important for writing robust, portable code.

Query optimization alchemy

Depending on the query plan, an optimizer might pivot the execution path, overlooking the anticipated short-circuit logic.
Even with different inputs, parameterized queries may reuse execution plans, undeterred by the differing potential short-circuiting outcomes.

The deceit of short-circuiting

Logic would suggest early termination, but the optimizer may prefer another path, seeming less efficient but potentially more so.
Testing is your friend, especially when you consider the same query may behave differently when dealing with larger, production-scale databases.

Influential factors in optimization

Workload and data spread can significantly influence the optimizer's decisions.
The existence of indexes, your use of statistics, and the sheer size of your tables could impact evaluation order.

Best practices for reliable results

Rather than banking on short-circuit evaluation, focus on clear queries, proper indexing, and meaningful optimizer hints.
Understanding that consistency and predictability are more important than highly variable performance gains from a potentially unreliable short-circuit mechanism.