Column alias in order by and where

If you ever wonder why column alias can be used in order by and not in where clause, it’s the logical query processing order. Pinal Dave blogged about the complete order here.

SELECT
Name n, GroupName g
FROM  HumanResources.Department D
WHERE g = 'Executive General and Administration'

In the above query, from clause is executed first followed by where clause, and select goes last. So where clause is trying to use a column g which doesn’t exist until select completes execution and thus resulting in a “invalid column g” error.

SELECT
Name n, GroupName g
FROM  HumanResources.Department D
ORDER BY g

But order by in this above query is executed at the end after select has created a column g.

And order by lived happily ever after >:]

~Sam.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s