Why am I getting Invalid Column Name Errors?

Ever wondered why despite your WHERE statement coming after the SELECT statement in your query, you can’t use any column aliases you have in your SELECT statement in the WHERE clause but can in the ORDER BY clause?

Here’s an example that will give you an invalid column error:

SELECT name AS VendorName, accountnumber

FROM Purchasing.Vendor

WHERE vendorname LIKE 'a%'

ORDER BY VendorName

The reason for this is the order in which your query is processed is different to the order in which you write it.

Queries are written in this order:

SELECT

FROM

WHERE

GROUP BY

HAVING

ORDER BY

But are processed in this order:

FROM

WHERE

GROUP BY

HAVING

SELECT

ORDER BY

So if you reference a column alias from your select statement in your where clause you will get an error because the where clause is executed before the select statement.  As your select statement hasn’t yet been executed the alias doesn’t exist yet hence the “invalid column” error.

If you re-write this query as follows you will not get an error:

SELECT name AS VendorName, accountnumber

FROM Purchasing.Vendor

WHERE name LIKE 'a%'

ORDER BY VendorName