SQL - Is it possible to use alias in where?

Question

I tried the following SQL on this example playground page from W3Schools.

SELECT CustomerID AS Id, CustomerName AS Customer
FROM Customers
WHERE Customer="Alfreds Futterkiste";

But I get No value given for one or more required parameters. as response. It works if I use CustomerName instead of the alias.

Is this a fail of the playground test page or is it just not possible?

the `WHERE` clause is evaluated before `select`. Hence the `where` clause is not aware of the aliases you used. — Gurwinder Singh, May 04 '17 at 18:39
I tried the snippet from the question and it works on the playground page linked to. — Toxaris, May 04 '17 at 18:42
Maybe it depends on the browser since the DB is created in the browser. — Black, May 05 '17 at 22:33

RandomUs1r · Answer 1 · 2017-05-04T18:45:25.073

3

Looking at your code:

SELECT CustomerID AS Id, CustomerName AS Customer
FROM Customers
WHERE Customer="Alfreds Futterkiste";

To alias the customer table you can do something like:

SELECT c.CustomerID AS Id, c.CustomerName AS Customer
    FROM Customers c
    WHERE c.CustomerName="Alfreds Futterkiste";

Where c is now the alias for Customers

It is not possible to use a column alias in a where clause because the where clause executes BEFORE the select.

edited May 04 '17 at 18:45

answered May 04 '17 at 18:39

RandomUs1r

1

I think they meant if column aliases could be used, not table ones. But correct otherwise. – Timothy G. May 04 '17 at 18:40
1

Gotcha, updated answer – RandomUs1r May 04 '17 at 18:43
1

Yes Timothy G. is right, but still thank you! – Black May 04 '17 at 18:46

score 3 · Answer 2 · answered May 04 '17 at 18:43

3

Ugly, but can be convenient in larger, more complex queries

SELECT * FROM
(SELECT CustomerID AS ID, CustomerName AS Customer
FROM Customers) as A

WHERE Customer = "Alfreds Futterkiste";

answered May 04 '17 at 18:43

airstrike

1

Especially if you are going to need that reference more than once in the rest of the query – HLGEM May 04 '17 at 18:44

score 3 · Accepted Answer · answered May 04 '17 at 18:44

The WHERE clause is evaluated before select. Hence the where clause is not aware of the aliases you used.

So you need to use the original column name:

SELECT CustomerID AS Id, CustomerName AS Customer
FROM Customers
WHERE CustomerName="Alfreds Futterkiste";

If you must use alias in the where clause, you may use subquery or CTE (an overkill and may result in slower query):

SELECT * from (
    SELECT CustomerID AS Id, CustomerName AS Customer
    FROM Customers
) t WHERE Customer = "Alfreds Futterkiste";

to use the alias "customer" in the where clause. – Gurwinder Singh May 04 '17 at 18:47 — Gurwinder Singh, May 04 '17 at 18:47

score 1 · Answer 4 · answered May 04 '17 at 18:40

1

No you can't reference the column alias in the WHERE clause.

answered May 04 '17 at 18:40

Dessma

4 Answers4