Order of Execution of SQL Queries

Question

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 17-Nov-13 9:39am

Atul Khanduri

Updated 21-Apr-17 8:05am

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

**Zoltán Zörgő** · Accepted Answer · 2013-11-17T10:20:00

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.