I'm using MySQL and I have the following table employees: table.

I had an exercise in which I had to select the oldest person. I know the correct way to do that is with a subquery: SELECT name, dob FROM employees WHERE dob = (SELECT MIN(dob) FROM employees).

However, I did it like so: SELECT name, dob FROM employees HAVING dob = MIN(dob). Now this returns an empty set, but doesn't throw any errors. So what does it do exactly? I've read that MySQL allows to refer to columns from SELECT clause in HAVING clause, without any GROUP BY clause. But why does it return an empty set?

CodePudding user response：

When you use MAX (or other aggregate functions) in the select columns or the having clause, you cause an implicit GROUP BY () (that is, all rows are grouped together into a single result row).

And when grouping (whether all rows or with a specific GROUP BY), if you specify a column outside of an aggregate function (such as your dob =) that is not one of the things being aggregated on or something functionally dependent on it (for example, some other column in a table when you are grouping by the primary key for that table), one of two things will happen:

If you have enabled the ONLY_FULL_GROUP_BY sql_mode (which is the default in newer versions), you will receive an error:

In aggregated query without GROUP BY, expression ... contains nonaggregated column '...'; this is incompatible with sql_mode=only_full_group_by

If you have not enabled ONLY_FULL_GROUP_BY, a value from some arbitrary one of the grouped rows will be used. So it is possible your dob = MIN(dob) will be true (and it will definitely be true if all rows have the same dob), but you can't rely on it doing anything useful and should avoid doing this.