SQL

こんにちは、しきゆらです。
今回は、DBにてselect文でデータを取得するときの取得順についてメモしておきます。

基本的に、取得順についてはorder句で指定した順番で取得されます。
しかし、order句を指定しない場合もデータは取得することができますね。
この時の取得順について触れる機会があったので、調べた結果をまとめておきます。

経緯

今回直面したことの簡単な経緯をメモしておきます。

データベースへランダムにデータを追加し、その結果をCSVとして吐き出す、ということをしていました。
シード値を固定した後、ランダムな値をDBへ追加してその結果問題なく何度も同じ値を取得できているかを確認したかったわけです。

実際、シード値を固定しているためランダム値は何度も同じ値を返すことは確認できました。
しかし、データベースからデータを取得しCSVへ吐き出した結果の差分をとると、何か所か結果が異なる部分がありました。

よく確認すると、データがおかしいわけではなく、単純に値の並び順が異なるだけであり、全く異なる値が入っているわけではないことがわかりました。

この時のSQL文は以下のような形でした。

[SQL]
select * from hoge;
[/SQL]

単純なselect文のみで、orderは指定していませんでした。

orderなし時の取得順について

これに関しては、SQL的にはどうにも指定されていません。
仕様等は見つけられませんでしたが、調べてみるとDBの実装依存となるようです。

Oracle Databaseの表データは、データベースへの挿入時の順序にかかわらず、特定の順序では保管されません。ある列の昇順または降順で行を取得するには、そうするようにデータベースに指示する必要があります。

Part 5：ソート順序 | ORACLE

結果の行の表示には特定の順序がないことに気付いたでしょう。

http://MySQL :: MySQL 5.6 リファレンスマニュアル :: 3.3.4.4 行のソート

きちんと書かれているわけではありませんが、順序を指定しない場合は特別何らかのソートがなされるわけではないようです。

MySQLでORDER BYがついていないときに返す結果セットの並び順は定められていない。

MySQLでORDER BYをつけないときの並び順 – かみぽわーる

ブログ等を見てみると、どこも同じようなことが書かれています。
データの取得順は、追加順やどこかのカラム順となるわけではないようです。

まとめ

データを取得した順番を考慮しないような場合は問題ありませんが、何らかの順序をもとに処理を行う場合は、しっかりorder句で順番を指定しなければいけないことがわかりました。

今回はここまで。

おわり

こんにちは、しきゆらです。

今回は、タイトルに有るようにSQLのJOINに対する個人的な誤解が解けたのでそれをメモしておきます。

前半は事の経緯なので、無視しても大丈夫です。

そして、先人である巨人のURLはこちら。

SELECT構文：JOINを使ってテーブルを結合する – SMART

http://rfs.jp/sb/sql/s03/03_3.html#JOIN

また、SQLだけ見せろ！という方向け

今回のSQLサンプル

http://sqlfiddle.com/#!9/0a510a/2/0

ことのはじめ

そもそも、私はこれまでSQLでテーブルを作るとき、それぞれのテーブルをどう関連付けて取得すればいいのかわからず１つのテーブルにすべてのデータをぶち込んでいました。

いわゆる非正規の状態。

しかし、それではいかん、ということで表を分けるようにしました。

(なお、この段階ではまだJOINと言うものを知りません。)

必要なデータの取得は、Ruby等のプログラムで各テーブルからデータを取得し、結合・ソート等をしていました。

しかし、表を結合して必要なデータを表現するためにJOINを用いればいいということを(最近)知り、

何でもかんでもJOINしていたわけです。

そんななか、本日ぶつかった壁のお話をしましょう。

ほんだい

何でもかんでもテーブルをJOINしてデータを取得していた私。

ぶつかった壁というのは、「JOINすると特定のデータが取得できない」という問題です。

例えば、以下のような何かのメモを表すデータベースを考えます。

メモを表現するitemテーブル

[table id=5 /]

メモのタグを表現するtagテーブル

[table id=6 /]

メモとタグを紐付けるtag_mapテーブル

[table id=7 /]

これらをもとに、考えてみます。

やりたいこと

３つの表をまとめて、メモの内容とタグ名を一覧で取得したい。

もしタグが付いていなくても、ないことも知りたい。

解決案

こんな感じのSQLを書いてみる。

SELECT content, GROUP_CONCAT(tag.name separator ',') as tags FROM item JOIN (tag JOIN tag_map USING(tag_id)) USING(item_id) GROUP BY item_id;

なお、GROUP_CONCATは引数のものをセパレータで区切って結合してくれる関数です。

12.19.1 GROUP BY (集約) 関数 – MySQLリファレンス

https://dev.mysql.com/doc/refman/5.6/ja/group-by-functions.html#function_group-concat

こうすると、「content」と「tags」をいっぺんに取ってこれます。

やったね！！

もんだい

全てにタグが設定されていれば問題なく取得できます。

しかし、新しく以下のようなデータが入ってくると話は変わってきます。

[table id=8 /]

上記レコートが追加された状態で同じようにSQLを実行します。

変化がない！？Why！？！？！？！！？！？

かいせつ

単なるJOINや「from hoge, fuga, …」としてテーブルを結合するものを内部結合(INNER JOIN)と言います。

これは、条件にあったもののみを列挙する結合。

今回であれば、「item_idとtag_idが紐づけされているもの」が条件となります。

しかし、itemテーブルに追加したものはまだタグと紐づけされていません。

そうなると、内部結合では条件にマッチしていないため取得されません。

解決方法

この問題を解決するには、外部結合(OUTER JOIN)を使います。

これは、条件にあっていないものも含めて取得するもの。

上記SQLを外部結合に書き換えて、実行してみます。

SELECT content, GROUP_CONCAT(tag.name separator ',') as tags FROM item LEFT OUTER JOIN (tag JOIN tag_map USING(tag_id)) USING(item_id) GROUP BY item_id

SQL的には、「JOIN」の前に「LEFT OUTER」をつけただけ。

これだけで外部結合になります。

※LEFT OUTER JOINの他に、RIGHT OUTER JOINと言うものもあるらしいです。

つなげる方向が違うようですが、基本的にはどっちか片方に合わせておけば問題ないかと思います。

LEFTとRIGHTで動作上の違いとか取得時の違いとかがあれば教えてください・・・

結果は以下の通り。

きちんと、取得できていることがわかります。

タグがあるものは、タグ名も一緒に取得し、タグがないものはnullとして取得しています。

参考　SELECT構文：JOINを使ってテーブルを結合する – SMART

http://rfs.jp/sb/sql/s03/03_3.html#JOIN

今回のSQLサンプル

http://sqlfiddle.com/#!9/0a510a/2/0

まとめ

今回はSQLの内部結合と外部結合の違いを知りました。

条件に合うものだけが必要なのか、そうでないのかによって使い分ける必要が有ることを知りました。

まだまだSQLとは仲良くなれていないので、もっとSQLと戯れていきたいです。

今回はこのへんで。

おわり