Using DATEDIFF function in WHERE clause

Question

In the HAVING lessons, it was mentioned that 'WHERE cannot use aggregate functions within its condition'. However, in the Practice Exam 3, Question 3, we used DATEDIFF() in the WHERE clause instead of the HAVING clause. When I used DATEDIFF() as per below query, there was an Error Code: 1054. Unknown column 'to_date' in 'having clause'.

SELECT
    COUNT(emp_no)
FROM
    salaries
WHERE
    salary >= 104038
GROUP BY emp_no
HAVING DATEDIFF(to_date, from_date) > 365;

Answer 1

Hi Yi!
Thanks for reaching out.

The reason is that when MySQL evaluates the HAVING clause, the values of columns specified in the clause have not been evaluated yet.

Query order of execution

FROM and JOINs.
The FROM clause, and subsequent JOINs are first executed to determine
the total working set of data that is being queried
WHERE
GROUP BY
HAVING
SELECT
DISTINCT
ORDER BY
LIMIT / OFFSET.

Hope this helps.
Best,
Tsvetelin

Using DATEDIFF function in WHERE clause

Submit an answer

related questions