Having Without Group By in MySQL

Question

I have read multiple articles and now I am confused between 2 following statements.

If we use having without group by then whole table act as Single Group.
If we use having without group by then each table act as an individual Group.

Which One is Correct in MySQL? For example I have a table named ABC as Follow:

|   Wage    | 
_____________
|    4      | 
|    8      | 
|    28     | 
|    90     |

If We Use Following Query

select wage 
from ABC
having wage > 1

then all the records get printed. So each row works as indivisual group.

But If We Use:

select wage
from ABC
having wage = max(wage)

the no record get printed. So whole table works as a group.

So which one is correct and why this 2 queries shows different results.

*Which One is Correct in MySQL?* Both. Or none. What statement of these ones is true in each particular case is not related to HAVING clause presence. If any aggergate function is used then (1) is true else (2) is true. — Akina, Sep 23 '20 at 09:05

GMB · Accepted Answer · 2022-05-23T08:10:08.470

Don't use having without group by. Although MySQL supports that, this is not valid standard SQL, and the behavior you get will most likely be counter-intuitive.

The first query should be just a where clause:

select wage from abc where wage > 1

The second query just makes no sense: you have both an aggregated and a non-aggregated wage in the having clause. If you want the row that has the maximum wage, then you can order by and limit:

select wage
from abc
order by wage desc limit 1

Or if you want to allow ties, use a correlated subquery:

select *
from abc a
where wage = (select max(a1.wage) from abc)

Having Without Group By in MySQL

1 Answers1