📚
GROUP_CONCATの引数のNULLには気をつけよう!
MySQLにおけるCONCATやGROUP_CONCATは、データを一文字列に統合するのに便利な関数です。しかし、これらの関数の引数にNULLが含まれている場合、結合した結果もNULLになり、特にGROUP_CONCATでは意図しない挙動に気づきにくくなります。
CONCATの動作
CONCAT関数は、concatnate(連結する)という単語から想像できるように、すべての引数を連結した文字列を返します。
SELECT CONCAT('Hello', ' ', 'World');
-> 'Hello World'
しかし、この引数のいずれかがNULLである場合、結果は全体がNULLになります。
SELECT CONCAT('Hello', NULL, 'World');
-> NULL
GROUP_CONCATの動作
GROUP_CONCAT関数は、CONCAT同様に引数の値を結合して、その文字列をさらにグループ内で一文字列に統合する関数です。
以下は、work_histories(職歴)テーブルに対し、company_name(会社名)とcompany_unit(部署名)を結合して、さらにユーザーごとにそれらをまとめて出力するクエリです。
SELECT user_name,
GROUP_CONCAT(company_name, ' ', company_unit)
FROM work_histories
GROUP BY user_name;
-> Alice ABC Corp 営業部, XYZ Ltd 広報部
Bob DEF Inc 人事部
グループ内の値間はデフォルトではカンマ(,)で区切られます。
GROUP_CONCATでは、NULL値が含まれる行が結合結果に現れないため、結果の解析が困難になることがあります。
例値:
user_name | company_name | company_unit
------------|------------- |--------------
Alice | ABC Corp | 営業部
Alice | DEF Inc | NULL
Alice | XYZ Ltd | 広報部
結果:
Alice ABC Corp 営業部, XYZ Ltd 広報部
この結果は、Aliceが2社の職歴を持っているように見えますが、実際には3社の経歴を持っています。
私はこの罠にハマり、SQLの間違いに気づけないまま誤ったデータを抽出していました、、
CONCAT、GROUP_CONCATのNULL対策
COALESCEやIFNULLを使って、NULLを空文字列に置換して対策しましょう。
COALESCEは、引数の最初の非NULL値を返す関数です。
SELECT CONCAT('Hello', COALESCE(NULL, ''), 'World') AS result;
-> 'HelloWorld'
SELECT user_name,
GROUP_CONCAT(IFNULL(company_name, ''), ' ', IFNULL(company_unit, ''))
FROM work_histories
GROUP BY user_name;
例値:
user_name | company_name | company_unit
------------|------------- |--------------
Alice | ABC Corp | 営業部
Alice | DEF Inc | NULL
Alice | XYZ Ltd | 広報部
Bob | DEF Inc | 人事部
結果:
Alice ABC Corp 営業部, DEF Inc , XYZ Ltd 広報部
Bob DEF Inc 人事部
まとめ
CONCATは引数にNULLがあるとNULLを返すということを知っておかないと、GROUP_CONCATではその挙動に気づきにくいので気をつけましょう!
Discussion