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.
database examples select solution sql alias sql clause sql query Subquery WHERE
