SQL Logical Query Processing Order

This probably applies more to beginners or people who have been using SQL for a while but have been wondering why sometimes the query works in a certain way and sometimes it didn’t.

Before I read the book for the exam 70-461, I found it relatively easy to just plonk in some code in SQL Server Management Studio and Voila! it works. Well, not all the time.

For example, this works:

SELECT customer, YEAR(orderdate) AS OrderYear FROM orders WHERE YEAR(orderdate) = 2013

But this doesn’t:

SELECT customer, YEAR(orderdate) AS OrderYear FROM orders WHERE OrderYear = 2013

Most of us will normally just go: “Hey, it’s no biggie, I’ll just use the former and then find out why later.”, but most often we will be too busy to do so. ­čÖé

Here is the “keyed-in order” of a standard SQL Query:

  1. SELECT
  2. FROM
  3. WHERE
  4. GROUP BY
  5. HAVING
  6. ORDER BY

This is how it is supposed to be typed so that it does not throw a syntax error.

This is how SQL Server processes the query:

  1. FROM
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. SELECT
  6. ORDER BY

See the difference?

Let’s reference back to the first example:

SELECT customer, YEAR(orderdate) AS OrderYear FROM orders WHERE OrderYear = 2013

Since the query is processed in that order, SQL Server processes the WHERE clause before the SELECT clause and it has no clue of the alias “OrderYear”.. hence it fails.

Another common way to write queries with the ORDER BY clause is by using the alias:

SELECT customer, YEAR(orderdate) AS OrderYear FROM orders ORDER BY OrderYear

Again referring back to the Logical Query Processing Order, this query works because ORDER BY is processed after the SELECT clause, therefore SQL Server is fully aware of the alias “OrderDate”.

What are the benefits?

In my experience, I often re-format datetime field into something more readable, but this makes the query longer. Therefore to make code more elegant, I use the alias:

SELECT customer, CONVERT(VARCHAR(10), orderdate, 112) AS NewOrderDate FROM orders ORDER BY NewOrderDate

I find that knowing this makes me more efficient as I am coding with this in mind and make less mistakes.

If this helps anyone, do let me know. Thank you.

 

 

 

One thought on “SQL Logical Query Processing Order

Leave a Reply

Your email address will not be published.