Can you use an alias in the WHERE clause in mysql?

sql

subqueries

aggregate-functions

aliasing

byAlex Kataev·Oct 28, 2024

In MySQL, column aliases can't be used directly within the WHERE clause. For those instances where you need to filter data based on a calculated column, you must either repeat the actual expression involved or utilize a subquery. While aliases are supported by the ORDER BY clause, this convenience does not extend to WHERE. Here's how you would use a subquery:

SELECT * FROM (SELECT salary * 12 AS annual_salary FROM employees) as sub WHERE sub.annual_salary > 50000;

or directly use the expression in WHERE:

SELECT salary * 12 AS annual_salary FROM employees WHERE salary * 12 > 50000;

Wrap your head around WHERE and HAVING

Where is my alias in WHERE?

You might have encountered frustration when your alias, defined in the SELECT clause, is not recognized in your WHERE clause. WHY? Because WHERE clause gets evaluated before the SELECT clause, it absolutely has no clue about any aliases that you might have defined later in the SELECT clause.

Aggregate Functions: Let's 'HAVE' some fun

When dealing with aggregated data such as SUM(), AVG(), COUNT(), etc., we don't use WHERE. Instead, we embrace our friend HAVING clause. Since HAVING clause swings into action after the aggregation phase, it has no problem recognizing aliases!

-- HAVING some fun with our high-earning employees!
SELECT employee_id, AVG(salary) AS avg_salary 
FROM employees 
GROUP BY employee_id 
HAVING avg_salary > 50000;

Subqueries: Our Secret Weapon

When you need to wield some calculated power, say hello to subqueries to conjure up a derived table that filters out data based on the alias named in the outer query.

SELECT annual_salary FROM (
    SELECT employee_id, salary * 12 AS annual_salary FROM employees
) sub WHERE sub.annual_salary > 50000;

Into the depths of aliasing in SQL

Subqueries: Labeling made easy

You can keep your SQL neat and tidy with the magic wand of subqueries!

-- Employee details neatly labeled
SELECT employee_detail.annual_salary
FROM (
    SELECT employee_id, (salary * benefits_factor) * 12 AS annual_salary
    FROM employees
) AS employee_detail
WHERE employee_detail.annual_salary BETWEEN 60000 AND 120000; -- Only select the "rich" ones

This method creates a clean label within your subquery and uses it for the outer query's WHERE clause.

CASE: The "switchblade" of SQL

CASE expressions can save you from repeatedly referencing external aliases:

SELECT employee_id,
       salary,
       CASE
           WHEN salary > 70000 THEN 'High'
           WHEN salary > 50000 THEN 'Medium'
           ELSE 'Low'
       END AS salary_group
FROM employees
WHERE (
          CASE
              WHEN salary > 70000 THEN 'High'
              WHEN salary > 50000 THEN 'Medium'
              ELSE 'Low'
          END
      ) = 'Medium';  -- No aliases here, just Medium folks of our company.

In this case, (pardon the pun!) the CASE expression is replicated in both SELECT and WHERE.

Post-aggregation filtering with HAVING

Filtering after aggregation is a common requirement in data analysis. HAVING is ready to tackle this job!

-- Showing off our “big sale” categories
SELECT category, SUM(sales) AS total_sales
FROM transactions
GROUP BY category
HAVING total_sales > 1000; -- Big spender here.