Yogesh Chauhan's Blog

How to reference an aliased column in the WHERE clause?

in SQL/MySQL on December 12, 2021

Read this post to learn more about SQL alias:

Learn how to give a temporary name to a column or to a table using SQL Aliases

SQL Column Alias Example

Let’s recap the post above and add column alias in a query.


SELECT firstname, userid AS id
FROM users

Reference alias

If you try to use that alias like this, you’ll get an error:


// THIS WILL NOT WORK

SELECT firstname, userid AS id
FROM users
WHERE id > 5

Why does this happen?

The WHERE clause is evaluated before the SELECT statement and at that point of execution, the alias id doesn’t exist in users table and you get an error.

How to fix this issue?

To fix this, we can create a virtual table using a subquery.

Subqueries are a way to perform SQL operations in multiple steps.

Since FROM is going to be evaluated before the WHERE clause, the virtual table will have id column by the time of execution of WHERE clause.

So, to make that work, you need to create a subquery like this:


// THIS WILL WORK

SELECT *
from (
SELECT firstname, userid AS id
FROM users
) as results
WHERE id > 5


The results is the name for the subquery results. You can skip it if you’re not referencing it into your query.


Most Read

#1 How to check if radio button is checked or not using JavaScript? #2 How to add Read More Read Less Button using JavaScript? #3 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #4 How to uninstall Cocoapods from the Mac OS? #5 PHP Login System using PDO Part 1: Create User Registration Page #6 How to Use SQL MAX() Function with Dates?

Recently Posted

#Jan 17 4 advanced ways to search Colleague #Jan 16 Colleague UI Basics: The Search Area #Jan 16 Colleague UI Basics: The Context Area #Jan 16 Colleague UI Basics: Accessing the user interface #Jan 14 How to display a student’s individual transcript in Colleague? #Jan 11 How to install PuTTY on a MacOS?
You might also like these
How to Access a Global Variable From Inside a Function in PHP?PHPHow to check if the page is single post page in WordPress?WordPressFlash of Invisible Text and Mitt Romney Web Font ProblemCSSHow to convert a number rounding to a specified number of decimals in JavaScript?JavaScriptHow to Validate User Name, Email Address and URL in PHP?PHPLearn to create profile card using HTML and CSSCSS