One Common SQL Programming Mistake (Red-Gate)

Predicate evaluation order

If you are familiar with the logical query processing order, then you may expect that a query is executed in the following order:


The sequence above outlines the logical order for executing query. Logically the FROM clause is processed first defining the source data set, next the WHERE predicates are applied, followed by GROUP BY, and so on.

However, physically, the query is processed differently and the query optimizer is free to move expressions in the query plan in order to produce the most cost efficient plan for retrieving the data. This leads to a common misunderstanding that a filter in the WHERE clause is applied before the next phases are processed. In fact, a predicate can be applied much later in the physical execution plan. Also, there is no left to right order for execution of predicates. For example, if you have a WHERE clause containing „WHERE x=1 AND y=2“, there is no guarantee that „x=1“ will be evaluated first. They can be executed in any order.

via Ten Common SQL Programming Mistakes.

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert