Using DATEDIFF function in WHERE clause
in SQL / Welcome to SQL
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'.
salary >= 104038
GROUP BY emp_no
HAVING DATEDIFF(to_date, from_date) > 365;
1 answers ( 0 marked as helpful)
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
- GROUP BY
- ORDER BY
- LIMIT / OFFSET.
Hope this helps.