Geek Answers Handbook

What does MySQL "GROUP BY NULL" do?

I have some old code that I support, and there is an SQL query that I don’t understand. I hope someone here can explain this goal to me. The request is as follows:

select * from product_performance where merchantid = 2151277 and clickday >= '2011-09-01' group by null;

When I run this query without group by null at the end, I get 44 rows. When I run it with group by null , I get 1 line - the first line in the previous set. What is going on here and what is the purpose of using this grouping?

+4
source share
2 answers

It groups the entire query by a constant value, which is why it selects a row that can be randomly returned. You can get the same results using LIMIT 1 .

Please note that this idiom is not standard SQL and may cause errors if you try it in other databases. In MySQL, you can disable this behavior by specifying ONLY FULL GROUP BY SQL mode. See the documentation for more details.

OTOH, LIMIT 1 also non-standard, but more widely supported, and the same effect can be achieved in databases conforming to SQL: 2008 with the FETCH ... ONLY clause. See more about this.

+7
source

hehe, let me explain in this mode: if you have

 select emp_id, dept_id from employees group by dept_id

you will get one row for each unit, and one emp_id, not guaranteed by anyone. This means that big data is being poured into departments.

if you shoot

 select emp_id, dept_id from employees group by dept_id, emp_id

You will get data separated by departments and emps.

But if you are group by null , it means that you are not sharing anything. You have one group. And mysql will provide you with emp_id and a department not guaranteed from which row.

0
source

More articles:


All Articles