「GROUP BY」と「HAVING」のどちらを使えばいいのか？

Question

MySQL5.7使用です。
GROUP_CONCATを書くとWHEREで存在しない条件なのに、レコードがNULLで取得されてしまう仕様を知りました。

解決策としてGROUP BYとHAVINGがあるようですが、どちらを使えばいいのでしょうか？
またその理由を教えて頂けませんでしょうか？

今回は以下の例としての質問ですが、「他にこういう例ならこっちがいい」なども知りたいです。

テーブル例

CREATE TABLE persons (
   ID INT AUTO_INCREMENT PRIMARY KEY
  ,age INT
  );
  
INSERT INTO persons(age) values (50),(20),(71),(33),(18);
 
CREATE TABLE children (
   person_ID INT
  ,person_ID2 INT
  );
  
INSERT INTO children(person_ID,person_ID2) values (1,2),(1,5),(3,4);

GROUP BY による方法

SELECT p.ID
      ,GROUP_CONCAT(c.person_ID2) AS children_ids

FROM persons p
     LEFT JOIN children c ON c.person_ID = p.ID

WHERE p.age > 100

GROUP BY p.ID

HAVING による方法

SELECT p.ID
      ,GROUP_CONCAT(c.person_ID2) AS children_ids

FROM persons p
     LEFT JOIN children c ON c.person_ID = p.ID

WHERE p.age > 100

HAVING children_ids IS NOT NULL

###追記
HAVINGではWHEREが存在するときの取得がおかしかったです。
次のようにWHERE p.age > 1とすると、ID=1 の子供は 2,5 だけなのに、2,5,4 が取得できてしまいました。

SELECT p.ID
      ,GROUP_CONCAT(c.person_ID2) AS children_ids

FROM persons p
     LEFT JOIN children c ON c.person_ID = p.ID

WHERE p.age > 1

HAVING children_ids IS NOT NULL

そうなると新たな疑問です。
こちらの質問で、ランキング上位の方がGROUP BYではなくHAVINGを提案されているのですが、これは間違ったご回答になるのでしょうか？

しかし的確なご回答ばかりされている方なので、とても間違いだとは思えません。
HAVINGが良い理由を知りたいです。

Comments

[m.ts10806]

テーブル定義とデータと取得したいデータ（要件）によると思います

[macof]

元の質問は単に集約キーを指定せず集約関数を使用した際 0レコードなら空集合になって欲しいと言う要件でしか無いので、根本的に話が異なると思いますが…
因みに追記されたSQLにおけるIDカラムは集約キーに指定されていないため、偶然1になっているに過ぎません。

Answer 1

前提としてgroup byとhavingはどちらと比較するものではないのでSQLの理解を誤っています

基本的にGROUP BY (集約) 関数
を使う場合、集約対象外の列をgroup byしなくてはいけません

mysql> SELECT p.ID
    ->       ,GROUP_CONCAT(c.person_ID2) AS children_ids
    -> FROM persons p
    ->      LEFT JOIN children c ON c.person_ID = p.ID
    -> GROUP BY p.ID
    -> ;
+----+--------------+
| ID | children_ids |
+----+--------------+
|  1 | 5,2          |
|  2 | NULL         |
|  3 | 4            |
|  4 | NULL         |
|  5 | NULL         |
+----+--------------+

havingはgroup byした列を対象に条件指定する時に使うもので、上のSQLのgroup byの後に付ける事で例えば不要な行（null行など）を除外する事ができます

having children_ids is not null;
+----+--------------+
| ID | children_ids |
+----+--------------+
|  1 | 5,2          |
|  3 | 4            |
+----+--------------+

whereは上記関数の当てる対象行を絞る為に使います
具体的には上のSQLのgroup byの前にwhereを追加して使います

where c.person_ID2>2
+----+--------------+
| ID | children_ids |
+----+--------------+
|  1 | 5            |
|  3 | 4            |
+----+--------------+

基本的にと書いたわけ

mysqlがまさに例外で、group byがなくても動いてしまう　これは自分で調べてください

Comments

[ThouShaltNot]

それぞれの使い方が理解できました。ありがとうございます。

Answer 2

havingはgroup byの過程で実施されます。
GROUP_CONCATも同様にgroup byの過程での実施です。

ですので、GROUP_CONCATの結果がNullの場合を除外するには、havingである必要があります。

ただ、誤解されているようなので補足しておくと、GROUP_CONCATの過程でのNullは除外されます。
1,2,3,Nullのような結果は返却されません。
すべてがNullだった場合だけNullが返却されるのです。

HAVINGではWHEREが存在するときの取得がおかしかったです。
次のようにWHERE p.age > 1とすると、ID=1 の子供は 2,5 だけなのに、2,5,4 が取得できてしまいました。

データ作成時にIDの値を指定していないから、意図しない値になっているという事はありませんか？
実際のデータを確認してみて下さい。

それから、質問のSQLではGROUP_CONCATは全体に対して行われています。
ID毎に行う場合は、GROUP BY IDが必要です。

Comments

[ThouShaltNot]

なるほどです。後半の「データ作成時にIDの値を指定していないから、意図しない値になっている」というのはどういう意味でしょうか？以下、11行目にIDのカラムを追加しましたが、
http://sqlfiddle.com/#!9/dad283/1
やはり「ID=1 の子供は 2,5 だけなのに、2,5,4 が取得できてしまいました。」の結果は変わりませんでした。

[ThouShaltNot]

「データ作成時にIDの値を指定していないから、意図しない値になっている」というよりも、「データ取得時にIDの値で集約していないから、意図しない値になっている」と仰りたかったのではないでしょうか？
以下、IDの値で集約すれば、意図した値になります。
http://sqlfiddle.com/#!9/2a622a/1

[sazi]

children のperson_IDの値はpersons のIDがそうなる前提ですけど、ID はAUTO_INCREMENTになっていますので、データをdeleteして再度insertした場合には、1から始まる訳では無いので、その可能性が無いかという意味です。
こういった値を指定する場合はpersons へのinsert時にIDの値を明示するべきです。

[sazi]

質問のSQLは手段なので、目的（やりたいこと）と一致しているかどうかは分からないのです。
> ID=1 の子供は 2,5 だけなのに、2,5,4 が取得できてしまいました。

という事から察するに、先ずは、GROUP BY IDが不足しているという事だと思いますが、
そもそもは、group_concat()は集計関数であるという事、またはGROUP BYに関する理解が不足しています。

[ThouShaltNot]

詳しくありがとうございました。

Answer 3

HAVINGはGROUP BYしないと

SELECT p.ID
,GROUP_CONCAT(c.person_ID2) AS children_ids
FROM persons as p
LEFT JOIN children c ON c.person_ID = p.ID
AND p.age>100
GROUP BY p.ID
HAVING children_ids IS NOT NULL

Comments

[ThouShaltNot]

ソースコードから意図が測る力量がなく、仰りたいことがわかりませんでした。