Using column alias in WHERE clause of MySQL query produces an error

Breaking down the alias limitation

Aliases: Scope and Skyscrapers

In SQL, Aliases are like names on a skyscraper - visible only from a certain point. They're unknown in the WHERE clause, as the WHERE clause is processed before the aliasing in the SELECT clause.

Clauses that invite aliases to the party

Even if WHERE clause doesn't entertain aliases, the GROUP BY, ORDER BY, and HAVING clauses open their arms wide. They are processed after the SELECT clause in the query execution timeline:

SELECT YEAR(sale_date) AS sale_year, COUNT(*) AS total_sales
FROM sales
GROUP BY sale_year
HAVING total_sales > 100
ORDER BY sale_year; -- Alphabets taught me B comes after A.


Alias workaround for WHERE

If the need is to filter on an aliased column, using a subquery is a viable option. Define the aliases within the subquery and then apply your WHERE clause logic:

SELECT * FROM (SELECT name, YEAR(birth_date) AS birth_year FROM people) AS tmp
WHERE tmp.birth_year < 2000; -- Y2K bug alert!


Or, you can go non-standard and use a HAVING clause without GROUP BY to filter using an alias:

SELECT name, YEAR(birth_date) AS birth_year
FROM people
HAVING birth_year < 2000; -- 80s kids, assemble.


Efficiency check

Expression repetition: The good, the bad, the ugly

Repeating the entirety of expressions in your WHERE clause makes sense, but at the cost of efficiency in complex queries. It's the database engine doing the math, for every single row, every single time.

Cleaning up with variables

Storing the repeated expressions within variables can tidy up and optimize your SQL code:

SET @threshold := 100;
SELECT (column1 + column2) AS total FROM table1 WHERE (column1 + column2) > @threshold; -- Seems like 101 is the lucky number today!


Subquery speed insights

Balancing the use of subqueries can yield the desired results and also keep your efficiency in check. Embrace the culture of testing and benchmarking query routes:

SELECT * FROM (SELECT (column1 + column2) AS total FROM table1 WHERE (column1 + column2) > 100) AS sub -- Would have been faster without the alias roadblock.


PASSWORD TO THE ALIAS KINGDOM

Unlock with nested selection

Here's the key that opens the nested SELECT statements door. Multiple layers of computations - the aliases from the inner layers are referenced on the outskirts:

SELECT outer_alias.total
FROM (SELECT (column1 + column2) AS total FROM table1) AS outer_alias
WHERE outer_alias.total > 100; -- Unlock and unleash.


Side-step errors and stay pally with aliases

When dealing with SQL, a little TLC can save the day. Keep errors - 'unknown column' at bay by verifying that aliases are invited where they're allowed, and are correctly spelled.

WHERE vs HAVING - What’s your pick?

Understanding when to use WHERE versus HAVING can filter through and streamline your queries, ensuring faultless execution. Arm yourself with the knowledge that HAVING filters results post an aggregation, while WHERE filters rows pre them.