MySQL HAVING clause not working with ' ' nor " ", instead works without AS statement

Question

SELECT 
    department_id, ROUND(MIN(salary), 2) AS 'Min Salary'
FROM
    employees
GROUP BY department_id
HAVING 'Min Salary' > 800;

This doesn't seem to work, but instead this:

SELECT 
    department_id, ROUND(MIN(salary), 2) AS min_salary
FROM
    employees
GROUP BY department_id
HAVING min_salary > 800

works just fine. Can someone give an answer to why i cant make a HAVING clause with ' ' or " ", but instead i have to use the column name?

score 3 · Accepted Answer · edited Jan 25 '20 at 19:32

3

HAVING 'Min Salary' > 800

The single quotes around the identifier turn it to a literal string. So this condition is actually checking if string 'Min Salary' is greater than 800. When cast to a number, 'Min Salary' becomes 0, which is smaller than 800. This is not what you expect.

Also, note that MySQL treats double quotes just like single quotes - whereas in some other databases like Oracle or Postgres, they are used to quote identifiers.

If you want to have a identifier that contains a space, then you need to use backticks, which is how MySQL quotes identifiers. So:

SELECT department_id, ROUND(MIN(salary), 2) AS `Min Salary`
FROM employees
GROUP BY department_id
HAVING `Min Salary` > 800;

edited Jan 25 '20 at 19:32

Strawberry

33,750
13
40
57

answered Jan 25 '20 at 19:09

GMB

216,147
25
84
135

1

Or you can `set sql_mode=ANSI_QUOTES` and then use double-quotes as identifier delimiters. – Bill Karwin Jan 25 '20 at 19:12
2

or, better still, don't include spaces in table/column names – Strawberry Jan 25 '20 at 19:32

MySQL HAVING clause not working with ' ' nor " ", instead works without AS statement

1 Answers1