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'?

Drift

Answer 1

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 has not yet occurred.

Henshaw

Rather than "backwards" I think it makes more sense to say "inside out" – Thorathoracic

It makes more sense to say that the entire statement is parsed, transformed, and optimised as a whole in a complex, multistage process. "SQL is evaluated backwards, from right to left" is just wrong – Wellgroomed

incomplete answer as the user asked if 'user_name' can be used in the statement, which it can be after e.g. 'HAVING' – Rudolf

It makes more sense, ACKSHULLAY, to explain in detail exactly how SQL works and to never ever summarize anything for the sake of brevity, clarity, or understanding. Clearly. – Adelaideadelaja

Answer 2

What about:

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

Bloom

Why would you use HAVING instead of WHERE in this case? – Liripipe

@Liripipe refer to Paul Dixon's answer. tldr; HAVING is evaluated later than WHERE and, more importantly, SELECT. – Indene

Answer 3

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."

(...)

It is not permissible to refer to a column alias in a WHERE clause, because the column value might not yet be determined when the WHERE clause is executed. See Section B.5.4.4, “Problems with Column Aliases”.

Sian

Also, note (from the MySQL man): The SQL standard requires that HAVING must reference only columns in the GROUP BY clause or columns used in aggregate functions. However, MySQL supports an extension to this behavior, and permits HAVING to refer to columns in the SELECT list and columns in outer subqueries as well. – Denni

Answer 4

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.

Ihs

Answer 5

If you're trying to perform a query like the following (find all the nodes with at least one attachment) where you've used a SELECT statement to create a new field which doesn't actually exist in the database, and try to use the alias for that result you'll run into the same problem:

SELECT nodes.*, (SELECT (COUNT(*) FROM attachments 
WHERE attachments.nodeid = nodes.id) AS attachmentcount 
FROM nodes
WHERE attachmentcount > 0;

You'll get an error "Unknown column 'attachmentcount' in WHERE clause".

Solution is actually fairly simple - just replace the alias with the statement which produces the alias, eg:

SELECT nodes.*, (SELECT (COUNT(*) FROM attachments 
WHERE attachments.nodeid = nodes.id) AS attachmentcount 
FROM nodes 
WHERE (SELECT (COUNT(*) FROM attachments WHERE attachments.nodeid = nodes.id) > 0;

You'll still get the alias returned, but now SQL shouldn't bork at the unknown alias.

Reginaldreginauld

I was facing this exact problem, and came across your answer - thank you! Just to note, it is (understandably) a little slow on large databases, but I'm dealing with a stupid inherited database setup anyway. – Expertise

I believe you have an extra ( in your query before the (COUNT(*) which isn't closed anywhere. – Sevigny

But isn't the SELECT statement run twice? – Secundines

I'm by far no mysql expert but this seems very inperformat. I've experienced that nested selects make a query much slower. – Furbelow

Maybe refactor the subquery as a CTE, it feels cleaner for me. – Krongold

Answer 6

Your defined alias are not welcomed by the WHERE clause you have to use the HAVING clause for this

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

OR you can directly use the original column name with the WHERE

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

Same as you have the result in user defined alias as a result of subquery or any calculation it will be accessed by the HAVING clause not by the WHERE

SELECT u_name AS user_name ,
(SELECT last_name FROM users2 WHERE id=users.id) as user_last_name
FROM users  WHERE u_name = "john" HAVING user_last_name ='smith'

Diva

Answer 7

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.

Wellgroomed

Answer 8

corrected:

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

Shelter

Answer 9

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

Icj

Answer 10

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

Hartwig

Answer 11

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

Horde

Why would you want to use a sub-query? Much simpler without. – Liripipe

Answer 12

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."'";

Sigurd

What have you changed and why? Additionally, why did you post code that is widely open for SQL injections? – Blown

Answer 13

May be it helps.

You can

SET @somevar := '';
SELECT @somevar AS user_name FROM users WHERE (@somevar := `u_name`) = "john";

It works.

BUT MAKE SURE WHAT YOU DO!

• Indexes are NOT USED here
• There will be scanned FULL TABLE - you hasn't specified the LIMIT 1 part
• So, - THIS QUERY WILL BE SLLLOOOOOOW on huge tables.

But, may be it helps in some cases

Capella

Answer 14

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.

Evzone

I think you can use the alias in some RDBMS like MySql for instance. You're correct for Sql Server though. – Liripipe

Answer 15

Just had this problem.

Make sure there is no space in the name of the entity in the database.

e.g. ' user_name' instead of 'user_name'

Boony

Answer 16

I had the same problem, I found this useful.

mysql_query("SELECT * FROM `users` WHERE `user_name`='$user'");

remember to put $user in ' ' single quotes.

Untie

That code is widely open for SQL injection. Nobody should use this code to solve the given problem – Blown