Posts

Showing posts from November, 2014

Logical order of SQL Query execution

First of all we should know that logical and physical order of query execution are different but result must be the same. Every RDBMS can optimize the order in which parts of query are executed for performance reason. However that rearrangement must not influence the final result. The query must return the same records as if it is executed in this logical order:     FROM     WHERE     GROUP BY     HAVING     SELECT     DISTINCT     ORDER BY     TOP X / LIMIT X/ select * from ( subquery ) ROWNUM <= X The programmers should always be aware of this sequence in order not to fall into a trap. A common mistake is to try using column alias in GROUP BY, WHERE and HAVING clause but actually it is possible to use it only after SELECT clause is executed (ORDER BY and TOP/LIMIT/that long stupid oracle boilerplate). Another mistake is to try sorting by column  that is not listed in SELECT clause. The less common but hard to debug problem could be made if OUTER JOIN is used