Unknown Column In Where Clause

Question

I have a simple query:

SELECT u_name AS user_name FROM users WHERE user_name = "john";

I get "Unknown Column 'user_name' in where clause". Can I not refer to 'user_name' in other parts of the statement even after select 'u_name as user_name'?

Answer 1

No you cannot. user_name is doesn't exist until return time.

Answer 2

No you need to select it with correct name. If you gave the table you select from an alias you can use that though.

Answer 3

corrected:

SELECT u_name AS user_name FROM users WHERE u_name = 'john';

Answer 4

SQL is evaluated backwards, from right to left. So the where clause is parsed and evaluate prior to the select clause. Because of this the aliasing of u_name to user_name as not yet occurred.

Answer 5

Either:

SELECT u_name AS user_name
FROM   users
WHERE  u_name = "john";

or:

SELECT user_name
from
(
SELECT u_name AS user_name
FROM   users
)
WHERE  u_name = "john";

The latter ought to be the same as the former if the RDBMS supports predicate pushing into the in-line view.

Answer 6

select u_name as user_name from users where u_name = "john";

Think of it like this, your where clause evaluates first, to determine which rows (or joined rows) need to be returned. Once the where clause is executed, the select clause runs for it.

To put it a better way, imagine this:

select distinct(u_name) as user_name from users where u_name = "john";

You can't reference the first half without the second. Where always gets evaluated first, then the select clause.

Answer 7

While you can alias your tables within your query (i.e., "SELECT u.username FROM users u;"), you have to use the actual names of the columns you're referencing. AS only impacts how the fields are returned.

Answer 8

Not as far as I know in MS-SQL 2000/5. I've fallen foul of this in the past.

Answer 9

See the following MySQL manual page: http://dev.mysql.com/doc/refman/5.0/en/select.html

"A select_expr can be given an alias using AS alias_name. The alias is used as the expression's column name and can be used in GROUP BY, ORDER BY, or HAVING clauses."

Answer 10

SELECT user_name FROM ( SELECT name AS user_name FROM users ) AS test WHERE user_name = "john"

Answer 11

Unknown Column in where clause Caused by lines 1 and 2 and resolved by line 3

1. $sql = "SELECT * FROM users WHERE username =".$userName;
2. $sql = "SELECT * FROM users WHERE username =".$userName."";
3. $sql = "SELECT * FROM users WHERE username ='".$userName."'";

hope this helps

Answer 12

very good Foripepe!

You save my live!

Thanks!

From Brazil

Answer 13

What about: SELECT u_name AS user_name FROM users HAVING user_name = "john";