MySQLでのGROUP BYの処理
問題演習を行っていた際に躓いた箇所があったため、備忘録として記録します。
以下のリンクの問題で私は
私の回答
SELECT
p.name '名前', p.position 'ポジション', p.club '所属クラブ', count(g.id) 'ゴール数'
FROM
players p
LEFT JOIN goals g ON g.player_id = p.id
GROUP BY p.name
ORDER BY 'ゴール数' DESC;
と上記のように回答しました。
しかし、正解は
正解
SELECT
p.name AS '名前', p.position AS 'ポジション', p.club AS '所属クラブ', COUNT(g.id) AS 'ゴール数'
FROM
players p
LEFT JOIN goals g ON g.player_id = p.id
GROUP BY p.id, p.name, p.position, p.club
ORDER BY 'ゴール数' DESC
です。
私の回答と正解とで異なる点は「GROUP BY」が指定するカラムです。
私の回答では「p.name」だけであったのに対し、正解では「p.id, p.name, p.position, p.club」とplayersテーブルから抽出する全てのカラムを指定しています。
MySQL 5.6 リファレンスマニュアルによると、
標準 SQL では、GROUP BY 句を含むクエリーは、GROUP BY 句で名前が指定されていない選択リスト内の非集約カラムを参照できません。たとえば、このクエリーは、選択リスト内の name カラムが GROUP BY に表示されていないため、標準 SQL では不正です。MySQL では、選択リストが GROUP BY 句で名前が指定されていない非集約カラムを参照できないように、GROUP BY の使用が拡張されています。つまり、上記のクエリーは MySQL では正当です。この機能を使用すると、不要なカラムのソートおよびグループ化が回避されるため、パフォーマンスを改善できます。ただし、これは主に、GROUP BY で名前が指定されていない各非集約カラム内のすべての値がグループごとに同じである場合に役立ちます。サーバーは各グループから任意の値を自由に選択できるため、同じ値でなければ、選択した値は不確定です。さらに、ORDER BY 句を追加しても、各グループからの値の選択が影響を受ける可能性はありません。値が選択されたあとに結果セットのソートが発生しますが、ORDER BY によって、サーバーで選択された各グループ内の値は影響を受けません。
とのように記述があります。
つまり、私の回答はMySQLにおいては不備にはならないけれども、GROUP BY で名前が指定されていないカラム内の値がグループごとに異なる場合はエラーとなるので気を付けましょうってことです。今回はたまたま、値がおんなじであったためエラーとはならずに出力できました。
まとめ
今後「GROUP BY」を使用する際は、クエリ内の「FROM」で指定したテーブルの全てのカラムを指定することを誓います。
リンク
MySQL 5.6 リファレンスマニュアル：https://dev.mysql.com/doc/refman/5.6/ja/group-by-handling.html
