using column alias in where clause MySQL

Question

I have two queries run on mysql. The first one returns an error while the seconds one returns an empty set and no error happens.

SELECT CONCAT_WS(',' , firstName, lastName) `Full Name` 
FROM employees 
WHERE `Full Name` LIKE '%on';

ERROR 1054 (42S22): Unknown column 'Full Name' in 'where clause'

SELECT CONCAT_WS(',' , firstName, lastName) 'Full Name' 
FROM employees
WHERE 'Full Name' LIKE '%on';

Empty set (0.00 sec)

Another odd thing about the second query is that it should return some rows ( notice Patterson)!

Can someone help me explain the difference between these two queries and why the second one doesn't return the correct result?

Answer 1

In your second query, you're essentially comparing the string "Full Name" to %on which always evaluates to false, and will therefore will never return results. Further, if you were to alter that to 'Full Name' like '%me', you'd get all of the records back because that expression always evaluates to true.

I've seen it said that it's not possible to use aliases in where clauses, but I think that may be inaccurate. Here, the backtick (`) character is acting like a delimiter for table, column, and field names whereas the apostrophe (') is not; that denotes strings and other values. Assuming that's true, you should be able to drop the backticks if you change the alias to something like full_name.

Answer 2

If I am understanding your problem correctly, you are not setting your AS (Firstname,Lastname) as a variable and therefore it cannot be used in your WHERE. You are LITERALLY comparing "Firstname, Lastname" to %on% -- Since you are using spaces in the AS instead of an underscore or camelCase, the ticks will not do what you want, and therefore the need for a single or even double quote .. ' OR " will suffice if there is a space in the AS

Also no need for CONCAT_WS if you are only combining 2 fields

To get your WHERE clause to compare to actual results, you also have to conacat --

SELECT CONCAT(firstName, ',' , lastName) AS 'Full Name' 
FROM employees
WHERE CONCAT(firstName, ',' , lastName) LIKE '%on';