Search code examples
mysqlsqlstringgroup-byhaving-clause

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

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?

Solution

  • 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;