UNIONを利用してselectの結果を結合する場合、カラム名はUNION文の一番最初に書かれたSQL文のものが優先される。
その後のSQLの取得結果は、カラム名を見て同じものを同じ列に並べてくれるわけではなく、取得項目の順番のみで判別され、結合される。
例えば、1つめのselect結果が、
col1, col2, col3, col4
という順番で取得され、2つめのselect結果が
col1, col3, col2, col4
という順番で取得されていた場合、2つめのselect結果のcol3は、col2として、col2はcol3としてUNIONの取得結果が表示される。
項目名とは意味の異なるデータが表示されるので、まったく使えないデータとなる。
自分でSQL文を直接書いている場合には考えられない現象だが、フレームワークを用いてSQL文を生成している場合は、この落とし穴にはまりやすい。
その場合は、多少性能が落ちるかもしれないが、各取得結果をテーブルとみなしたサブクエリを用いて、取得項目の並びを指定して取得しなおすという方法が有効でした。
Mysqlを使ってシステムを組んでいると、いろんな条件でデータを取得したい場面がでてくる。その中で、使用頻度が高いのがgroup by、order by、サブクエリの組み合わせ。
group byをすると、指定したキーで一番最初に現れたものが残り、後から現れたものは削除される。order by と併用した場合は、group by した結果に対して、order byが適用される。
order by を適用した後に、group by を適用したい場合は、サブクエリを用いてorder by を実行するSQLをテーブルとみなして、group by をかけるという方法になる。
例えば、mixiのようなSNSで各ユーザの最新の日記を取得したい場合は、日記テーブルを作成日で、降順にソートするSQLをテーブルとみなして、ユーザIDでgroup by をかけるというサブクエリを利用すれば可能。
group by と order by だと group by が優先されるというのは、結構覚えておくと役にたつことが多かったです。
今やっているプログラムで使わざるを得なくなったUNION構文。
UNIONとUNION ALL何が違うんだろう?と調べてみました。
SQLをわかっている方向けに説明すると、行全体にdistinctをかけるのがUNIONで、かけないのがUNION ALL。
つまりUNIONで連結した場合、全てのカラムの値が同じ行が複数あった場合、1行を残してすべてまとめられます。
1
2
3
4
5
| ----------------------
| col_a | col_b |
----------------------
| 0 | a |
---------------------- |
こんな取得結果をUNIONとUNION ALLにて連結した場合。
1
2
3
4
5
| ----------------------
| col_a | col_b |
----------------------
| 0 | a |
---------------------- |
これがUNION
1
2
3
4
5
6
7
| ----------------------
| col_a | col_b |
----------------------
| 0 | a |
----------------------
| 0 | a |
---------------------- |
これがUNION ALL