Click here to Skip to main content
15,945,906 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I am curious about the order of execution of SQL Queries. I did a bit of research on this. I found this:

http://stackoverflow.com/questions/4596467/order-of-execution-of-the-query

In that link, someone stated the following:

"SQL has no order of execution. Is a declarative language. The optimizer is free to choose any order it feels appropriate to produce the best execution time. Given any SQL query, is basically impossible to anybody to pretend it knows the execution order. If you add detailed information about the schema involved (exact tables and indexes definition) and the estimated cardinalities (size of data and selectivity of keys) then one can take a guess at the probable execution order."

Really? There is NO ORDER???

But in the following link i got a logical processing order.

http://blog.sqlauthority.com/2009/04/06/sql-server-logical-query-processing-phases-order-of-statement-execution/


SQL
1. FROM
2. ON
3. OUTER
4. WHERE
5. GROUP BY
6. CUBE or ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10. ORDER BY
11. TOP


So i am a bit confused and now really very curious to know that there is actually any order or not...!!!!
Posted
Updated 21-Apr-17 8:05am
Comments
CHill60 17-Nov-13 16:22pm    
The real point is that the RDMS determines how best to execute a single SQL query ... this can depend on many factors and your first link actually explains how to view the execution plan (but be aware this may differ from day to day, other queries being run may affect it etc etc). When it comes to the order of sql queries (plural) then understand that SQL Server is not asynchronous, so the first query will complete, followed by the next query etc etc, if you are running more than one query in a script, procedure etc
ashok_n 5-Jun-15 3:17am    
Perfect and Very nice
Member 13375650 30-Aug-17 0:33am    
we need isolation so that 2 transactions can have no effect of each other ,but if suppose 2 transactions,transaction 1 updates a row then transaction 2 must have effect
so please clarify

1 solution

Well, it is not that simple. It is a declarative language, but of course it has a logic, an algorithm. But not in that sense. For a simple query it might look alike, but in general there is a so called query optimizer task, that is calculating a query plan for that specific query. A query plan is taking into account indexes, set sizes and many other circumstances to get a specific execution order.
Look here for some theory: http://infolab.stanford.edu/~hyunjung/cs346/ioannidis.pdf[^]
But if you google for "query optimizes" you will find several interesting articles about different RDBMS, since this is key feature of everyone, thus different.
 
Share this answer
 
Comments
Amol_B 21-Nov-13 4:09am    
+5 :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900