Lets start with something simple. I used to wonder why column aliases were valid in an ORDER BY clause, but not in a WHERE clause. It took me a few years to actually look this up and find out what’s going on, so today I’m sharing the answer with you in the hope it gets you there quicker.
Let’s say we have the following query.
SELECT c.CustomerID,
c.Forename + ' ' + c.Surname AS Fullname
FROM dbo.Customer c
The SELECT statement concatenates the Forename and Surname columns to make an output column called Fullname. Let’s just assume the concatenated values aren’t NULL or empty to make things easier here.
If we then want to narrow down our results by filtering on Fullname, then you may think adding the WHERE clause below would be fine.
SELECT c.CustomerID,
c.Forename + ' ' + c.Surname AS Fullname
FROM dbo.Customer c
WHERE Fullname LIKE 'Anne%'
However, running that gives us the following error.
Errors are bad, so to get our WHERE clause working, we would have to change the query to look like this.
SELECT c.CustomerID,
c.Forename + ' ' + c.Surname AS Fullname
FROM dbo.Customer c
WHERE c.Forename + ' ' + c.Surname = 'Anne%'
This involves a bit of extra typing, and there are reasons why doing string concatenation in a where clause will perform badly, but that’s for another post. This will give us the results we need for now.
So, what’s the point of this post? Have a look at what happens when we change our query again to order the results by Fullname.
SELECT CustomerID,
c.Forename + ' ' + c.Surname AS Fullname
FROM dbo.Customer c
WHERE c.Forename + ' ' + c.Surname LIKE 'Anne%'
ORDER BY Fullname
There’s no error message this time! What’s going on here, and why can’t we use Fullname in the WHERE clause when it works perfectly fine in the ORDER BY clause?
The answer is due the SQL Server’s order of execution. Even though we write the query in a certain order, this doesn’t mean SQL Server will execute each part of our query in that same order. What’s actually happening with our query is this.
FROM
WHERE
SELECT
ORDER BY
As you can see, the SELECT statement is evaluated after the WHERE clause but before the ORDER BY clause. At the point where the WHERE clause is evaluated, SQL Server doesn’t know about our concatenated Fullname column yet. At the point where the ORDER BY clause is evaluated, the SELECT clause has already been examined, and SQL Server will happily let us use the Fullname alias here.
Mystery solved. Thanks for reading!