postgres vs mysql with regard to group by-CodePudding

I have two tables albums and bands, and I want to see which albums don't have bands. I use the following Mysql query and it works fine:

select bands.name from bands 
left join albums on bands.id = albums.band_id 
group by albums.band_id 
having count(albums.id) = 0;

But in Postgresql, I get the following error:

Query 1: ERROR:  column "bands.name" must appear in the GROUP BY clause or be used in an aggregate function 
LINE 4: select bands.name from bands left join albums on bands.id = ...

Why is that? I feel like postgres is more sql compliant, no?

CodePudding user response：

I use the following Mysql query and it works fine:

It works fine because it depends on the sql_mode only_full_group_by which is disabled.

Enable it first by checking the sql_mode ,

mysql> select @@sql_mode;
 ------------------------ 
| @@sql_mode             |
 ------------------------ 
| NO_ENGINE_SUBSTITUTION |
 ------------------------ 
1 row in set (0.00 sec)

Enable it by ,

 set global sql_mode ='NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY';

You should add it on the .cnf file as well under the [mysqld]

[mysqld]

sql-mode="NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY"