SQL where with date functions

Question

i dont understand why we can use WHERE clause with date functions. For instance lets take

SELECT name FROM table1
WHERE DATEDIFF(year1,year2) < 10;

And its working. I thought that we cant use WHERE with functions. To do this we should use HAVING clause. So how it is for real? Are date functions not the same as MAX,MIN,AVG and so on;

Answer 1

It's not correct that you can't use functions in the WHERE clause. You can put almost anything in it (except aggregates) as long as it evaluates to a logical true/false condition based on the columns of the involved tables, or just constant expressions. HAVING on the other hand does support aggregates in addition to everything else.

The difference between the two is when it's executed, it's relevant when you use GROUP BY. WHERE comes first, it imposes conditions on which rows from all involved tables can be used to form the groups, and those that don't fulfill it won't be taken into account for aggregations. You can think of WHERE working on the raw table data before grouping takes place. HAVING is evaluated afterwards, when the groups are already formed and aggregations are computed (that's why you can use them here) and decided which final groups are allowed to show up in the final query result.

They both operate at different times and on different sets of data. Note that when there is no GROUP BY, WHERE and HAVING are equivalent.

Answer 2

You cannot use aggregate functions after WHERE clause because it makes no sense, whereas DATEDIFF is usable. It makes no sense because it would be like telling the computer:

select all the rows for which the maximum of the id column is 34

which may sound somewhat logical but is not really, and is in fact the same as id < 35. that second way is also way clearer.

The real problem is in fact that counting the max of a column in a selection requires that a selection already be made. Hence MAX can't be used as a condition to actually select rows to be evaluated.

as for DATEDIFF, it works because the resulting question, for the computer, is logical:

select all the rows for which the difference between date1 and date2 is less then 10