Search code examples
sqlsql-execution-plan

SQL chain AND operator, all executed?

I am wondering if in a SQL request using several AND statement, is every AND executed or are they triggered only when the previous AND is ok?

In this example :

SELECT * FROM my_table WHEN a > 5 AND b < 10

If the condition a > 5 is not respected, will the test b < 10 be triggered? Or as in JAVA will it be triggered only when a > 5 valid?

Thanks for your help ;)

Bye !

Solution

  • In general, SQL engines do implement "short-circuiting", meaning that execution of conditions stops when the final value is known.

    However, this is pretty unimportant. First, you cannot depend on the order of evaluation of conditions. So, you don't know which will be evaluated first. Second, the engine may use alternative execution paths (such as using indexes).

    And, finally, this short-circuiting typically has a minimal impact on performance. Reading the data is typically much more important. The one exception is when one of the conditions uses very complex functions. In that case, you would use case or subqueries to ensure the order of execution.