CODEWITHSUNDEEP
mysqlsqlselectsubquerywhere-clause
Cratylus
Cratylus

Reputation: 54074

Using column aliases in a WHERE clause

A WHERE is evaluated first before a SELECT column list. As a result the following:

SELECT l_n AS last_name FROM contacts WHERE last_name > 'J';

can not work.
The following will work:

SELECT * FROM (SELECT l_n AS last_name FROM contacts) tmp WHERE last_name > 'J';

My question is: Is this also classified as a subquery? And if yes, if I wanted to avoid using subqueries is there another alternative for this?

Upvotes: 1

Views: 98

Answers (2)

medina
medina

Reputation: 8159

Yes, here is...

SELECT l_n AS last_name FROM contacts HAVING last_name > 'J';

Keep in my that your query is gonna bring all the results (full scan) and then filter by your criteria on the Having clause.

You should use a subquery if you concerned about performance. Or rather:

SELECT l_n AS last_name FROM contacts HAVING l_n > 'J';

[EDIT]

Hey mate, yes it works with no aggregation function. here is a example:

mysql> select * from blah;
+----+---------+
| id | name    |
+----+---------+
|  1 | renato  |
|  2 | rodrigo |
+----+---------+
2 rows in set (0.00 sec)


mysql> select id as a1, name as a2 from blah having a1 > 1;
+----+---------+
| a1 | a2      |
+----+---------+
|  2 | rodrigo |
+----+---------+
1 row in set (0.00 sec)

My database is a 5.5.29-0ubuntu0.12.04.2.

Upvotes: -1

Dan Bracuk
Dan Bracuk

Reputation: 20804

To the question, " Is this also classified as a subquery", the answer is yes. To the question of avoiding subqueries, the easiest way is to use the actual column name in the where clause of your query.

Upvotes: 3

Related Questions