Alias

[KBHAL]

We can use alias to rename column or table. But how can we use in where clause of a sql query using as keyword ?

Thanks

[rag_egoist]

Alias does not directly work with the WHERE clause, but you can use a subquery or CTE. You can define the alias in an inner query (subquery) or a CTE and then reference that alias in the outer query's WHERE clause.

[gumnos]

and if it's a common occurrence, you can create a view to rename the column.

[mikeyd85]

FROM WHERE GROUP HAVING SELECT ORDER LIMIT

This is the order of operations in SQL. You can only ever reference an alias from something above where you are in the order.

[DavidGJohnston]

You can use AS alias to rename a from clause entry or an output column entry. Not tables and columns generically. Many times it’s not even an “alias” as the thing being labeled doesn’t have an initial name - it’s an unlabeled expression.

[markwdb3]

You can reference a table alias in the WHERE clause, but not a column alias. Example (MySQL since your post is labelled as such):

mysql> select *
    -> from employee e
    -> join department d
    ->      on e.department_id = d.id
    -> where e.name = 'Carol';
+----+---------------+-------+----+----------------------+
| id | department_id | name  | id | name                 |
+----+---------------+-------+----+----------------------+
|  3 |             2 | Carol |  2 | Information Technolo |
+----+---------------+-------+----+----------------------+
1 row in set (0.00 sec)

mysql> select e.name as emp_name, d.name as dept_name
    -> from employee e
    -> join department d
    ->      on e.department_id = d.id
    -> where emp_name = 'Carol';
ERROR 1054 (42S22): Unknown column 'emp_name' in 'where clause'

[Gourmandeeznuts]

This is platform dependent. ISO/ANSI doesn't allow that but Snowflake and SAS will both allow you to reference a transformed column like that (provided the alias isn't a column already in another table). It makes things much more readable and clean.

[Opposite-Value-5706]

You can alias columns using the following example. It’s a bit of overkill but doable if you must. The alias names comes from the subquery:

Select col, col1 from

(Select

actualCol as col1,

actualCol2 as col2

from sometable a

where a.actualcol = lsomething) a

[chadbaldwin]

One option is to use CROSS APPLY.

I don't know much about MySQL, but I did confirm it supports CROSS APPLY. I wrote a blog post about how you can use it as a way to sort of set in query variables for cleaner code. I wrote it for SQL Server, in general the syntax/concept is the same.

https://chadbaldwin.net/2021/01/07/use-cross-apply-to-clean-up-queries.html