column in SELECT is "undefined" in WHERE

Question

I have a query:

SELECT exhibitor_participation+0 AS exhibitor_participation_value 
FROM `exhibitor_registry` 
WHERE ((exhibitor_participation_value & 1) = 1)

Question:

Why does it return this:?

Unknown column 'exhibitor_participation_value' in 'where clause'

Isn't it supposed to be successful since I defined exhibitor_participation_value in the SELECT clause?

Answer 1

It might depend on your database but generally no. Column aliases defined in the select clause are not available in the where clause.

Answer 2

The where clause is operating on the raw columns of the dataset. The select clause at the same level operates on the same thing, and can optionally give aliases for outer layers of code. But critically, the aliases "wrap" the current level and aren't available there - specifically, you can't use the aliases in the where clause.

You could have done something like this:

SELECT *
FROM
    (SELECT exhibitor_participation+0 AS exhibitor_participation_value
     FROM `exhibitor_registry`)
WHERE ((exhibitor_participation_value & 1) = 1)

and the alias can be used in the where clause, since it's now the name of one of the columns being selected at that level. Pushing the select down into an inner view may limit the ability of the database to come up with an appropriate query plan (which as usual will depend on many factors and vary between each individual query). On the other hand, if the where condition takes a non-negligible amount of time to calculate, you may actually save time by calculating it once in the inner view rather than potentially twice (again, the optimiser may reuse the first value here - it depends).

As with all performance-related issues, profile to see what is actually needed in your specific situation.