[SQL] 名前(文字列)の DISTINCT に注意
id と name があるテーブルから重複なしで name 一覧を取り出したい場合、 DISTINCT name で実行すると思わぬ結果となるので注意が必要。この反省を忘れないようメモ。
経緯
CS50 の SQL の課題で、 name カラムから重複なしで全員分の名前を取得するという問題があった。取得件数は 18,013 件になるはずだが、自分の SQL 文だと 17,965 件になる。
しかも、このままサブミットしてもテストをパスしてしまうので、問題の間違いなのか CS50 グループで調べたらやはり 17,965 件が間違いであると分かった。
原因と正解
people テーブルの構造はこうなっていた。
PRAGMA table_info('people');
cid | name | type | notnull | dflt_value | pk
0 | id | INTEGER | 0 | | 1
1 | name | TEXT | 1 | | 0
2 | birth | NUMERIC | 0 | | 0問題の SQL 文
SELECT COUNT(DISTINCT name) FROM people INNER JOIN xxx ON ……;これだと、同姓同名 ( id は異なるが、名前が同じ ) がいる場合でも、カウントが 1 となってしまう。
name ではなく、 id で DISTINCT しなければならない。
なので、副問合せで id の重複を取り除き、それに紐づく name を取得した。
SELECT COUNT(name) FROM people
WHERE id IN (SELECT DISTINCT p.id FROM people p
INNER JOIN …...);