CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user5093738
user5093738

Reputation:

Logical query processing phase of INSERT, DELETE, and UPDATE in SQL queries

I am curious about the logical query processing phase of SQL queries.

For SELECT queries, the logical query processing phase order is:

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

What is the order for INSERT, for UPDATE and for DELETE?

Upvotes: 11

Views: 902

Answers (3)

ifoGuest
ifoGuest

Reputation: 1

I had the same question and could not find an answer on the internet. So i tried to logically derive the answer. Here is a simple UPDATE statement (with alias a for the table):

UPDATE tbl_employees a
  SET a.Name = 'Anna'
  WHERE a.Id = 122;

Obviously, whether SET nor WHERE can be performed before the table is identified, so UPDATE must be the first logical step. Proof: Alias a is working (in Microsoft Access).

Before applying the SET Statement, one needs to know what records to apply it on. So WHERE must go as the second logical step (omitting WHERE would alter all records in the table)

Applying the SET Statement on the WHERE-filtered recordset must be the third step.

Summing up, the logical processing order must be:

  1. UPDATE (~equivalent to FROM)
  2. WHERE
  3. SET (~equivalent to SELECT)

Any other order seems absurd (can you hypothetically think of any other order?).

Once again, its my own logical derivation. I dont know for sure. I would appriciate any link to a serious internet resource.

Upvotes: 0

Jonathan Allen
Jonathan Allen

Reputation: 70327

If you would like to know what the actual query processing order is, take a look at the execution plan. That will tell you step by step exactly what SQL Server is doing.

https://technet.microsoft.com/en-us/library/ms178071(v=sql.105).aspx

Upvotes: 1

Michael Betterton
Michael Betterton

Reputation: 114

SQL Server: Source

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

Upvotes: 0

Related Questions