Referencing an Aliased Column in the WHERE Clause via SQL query

SQL studing: specify Alias  in the WHERE Clause

Problem

You have used aliases to provide more meaningful column names for your result set and would like to exclude some of the rows using the WHERE clause. However, your attempt to reference alias names in the WHERE clause fails:

select sal as salary, comm as commission
  from emp
 where salary < 5000

Solution

By wrapping your query as an inline view, you can reference the aliased columns:

1 select *
2   from (
3 select sal as salary, comm as commission
4   from emp
5        ) x
6  where salary < 5000

Discussion

In this simple example, you can avoid the inline view and reference COMM or SAL directly in the WHERE clause to achieve the same result. This solution introduces you to what you would need to do when attempting to reference any of the following in a WHERE clause:

  • Aggregate functions
  • Scalar subqueries
  • Windowing functions
  • Aliases

Placing your query, the one giving aliases, in an inline view gives you the ability to reference the aliased columns in your outer query. Why do you need to do this? The WHERE clause is evaluated before the SELECT; thus, SALARY and COMMISSION do not yet exist when the “Problem” query’s WHERE clause is evaluated. Those aliases are not applied until after the WHERE clause processing is complete. However, the FROM clause is evaluated before the WHERE. By placing the original query in a FROM clause, the results from that query are generated before the outermost WHERE clause, and your outermost WHERE clause “sees” the alias names. This technique is particularly useful when the columns in a table are not named particularly well.

Tip

The inline view in this solution is aliased X. Not all databases require an inline view to be explicitly aliased, but some do. All of them accept it.

 

Вас заинтересует / Intresting for you:

A Brief History of SQL
A Brief History of SQL 2835 views Александров Попков Wed, 17 Oct 2018, 15:04:29
SQL: how to determine which ro...
SQL: how to determine which ro... 170 views Денис Tue, 06 Jul 2021, 18:47:53
How to Retrieving a Subset of ...
How to Retrieving a Subset of ... 244 views Денис Wed, 14 Jul 2021, 04:45:37
SQL: Locating the Beginning an...
SQL: Locating the Beginning an... 234 views Денис Sat, 10 Jul 2021, 18:02:52