表結合の条件指定で［on］が見かけられます。また、［on］を用いた構文に［where］を使っている例題は、調べた範囲では見つけることができません。
［on］と［where］との違いは何でしょうか？
なぜ、使い分ける必要があるのでしょうか？
この辺がこんがらがって、うまく理解を進めることができません。
また、結合構文の詳しく書かれた本を紹介していただきませんか？専門的で単調な記述法でなく、一般人にもわかりやすく例文が豊富で、ひとつの構文に対し多方向からの説明がなされている書籍ですとありがたいのですが・・・。
入門書は数冊読みましたが、前回の質問でな～んも理解していないことがわかりました。
http://q.hatena.ne.jp/1174972211
SQL文法をきちんと理解したいです。よろしくお願いします。
No.4
230
34
100pt
回答が大変長くなってしまいました。ご迷惑をお掛けしますが、よかったら、読んでください。
seadwellさんが既によく考えた例の方がわかりやすいと思いますので、前々回のご質問 http://q.hatena.ne.jp/1174771937 を具体例として、その質問のコメントに書いたＳＱＬを使って解説致します。※[カテゴリソート]フィールドと[サブカテゴリソート]フィールドを加え、それに伴ってＳＱＬを多少変更しました。※なお、LEFT JOIN～USINGを重ねる順番など、kurukuru-nekoさんから教え頂きました。
●カテゴリTB
[カテゴリID],[カテゴリ名],[カテゴリソート]
1, アウトドアスポーツ,C
2, 園芸 ,A
3, 建設機器 ,B
●サブカテゴリTB
[サブカテゴリID],[サブカテゴリ名],[サブカテゴリソート]
1, スクール , b
2, 販売 , a
3, レンタル , c
●カテtoサブカテTB
[カテゴリID],[サブカテゴリID]
1, 1
1, 2
2, 1
2, 3
3, 2
3, 3
●メンバーtoカテtoサブカテTB
[メンバーID],[カテゴリID],[サブカテゴリID]
1, 1, 2
1, 2, 3
2, 2, 1
2, 3, 2
●SQL文
SELECT A.カテゴリID, カテゴリ名, A.サブカテゴリID, サブカテゴリ名, IF(メンバーID IS NULL,0,1) AS 登録 FROM カテtoサブカテTB AS A LEFT JOIN カテゴリTB USING(カテゴリID) LEFT JOIN サブカテゴリTB USING(サブカテゴリID) LEFT JOIN メンバーtoカテtoサブカテTB AS B ON A.カテゴリID = B.カテゴリID AND A.サブカテゴリID = B.サブカテゴリID AND メンバーID = '1' ORDER BY カテゴリソート, サブカテゴリソート ;
●以下の(1)～(6)のように、SQLが実行され、結果テーブルが変化していくとイメージするとわかり易いと思います。
(1)FROM カテtoサブカテTB AS A
[カテゴリID],[サブカテゴリID] 1, 1 1, 2 2, 1 2, 3 3, 2 3, 3
(2)LEFT JOIN カテゴリTB USING(カテゴリID)
[カテゴリID],[サブカテゴリID][カテゴリ名],[カテゴリソート] 1, 1, アウトドアスポーツ, 3 1, 2, アウトドアスポーツ, 3 2, 1, 園芸 , 1 2, 3, 園芸 , 1 3, 2, 建設機器 , 2 3, 3, 建設機器 , 2
(3)LEFT JOIN サブカテゴリTB USING(サブカテゴリID)
[カテゴリID],[サブカテゴリID][カテゴリ名],[ソート],[サブカテゴリ名],[サブカテゴリソート] 1, 1, アウトドアスポーツ, C, スクール, b 1, 2, アウトドアスポーツ, C, 販売 , a 2, 1, 園芸 , A, スクール, b 2, 3, 園芸 , A, レンタル, c 3, 2, 建設機器 , B, 販売 , a 3, 3, 建設機器 , B, レンタル, c
(4)LEFT JOIN メンバーtoカテtoサブカテTB AS B
ON A.カテゴリID = B.カテゴリID AND A.サブカテゴリID = B.サブカテゴリID AND メンバーID = '1'
[カテゴリID],[サブカテゴリID][カテゴリ名],[カテゴリソート],[サブカテゴリ名],[サブカテゴリソート],[メンバーID] 1, 1, アウトドアスポーツ, C, スクール, b, NULL 1, 2, アウトドアスポーツ, C, 販売 , a, 1 2, 1, 園芸 , A, スクール, b, NULL 2, 3, 園芸 , A, レンタル, c, 1 3, 2, 建設機器 , B, 販売 , a, NULL 3, 3, 建設機器 , B, レンタル, c, NULL
(5)ORDER BY カテゴリソート, サブカテゴリソート
[カテゴリID],[サブカテゴリID][カテゴリ名],[カテゴリソート],[サブカテゴリ名],[サブカテゴリソート],[メンバーID] 2, 1, 園芸 , A, スクール, b, NULL 2, 3, 園芸 , A, レンタル, c, 1 3, 2, 建設機器 , B, 販売 , a, NULL 3, 3, 建設機器 , B, レンタル, c, NULL 1, 2, アウトドアスポーツ, C, 販売 , a, 1 1, 1, アウトドアスポーツ, C, スクール, b, NULL
(6)SELECT A.カテゴリID, カテゴリ名, A.サブカテゴリID, サブカテゴリ名, IF(メンバーID IS NULL,0,1) AS 登録
[カテゴリID],[カテゴリ名],[サブカテゴリID],[サブカテゴリ名],[登録] 2, 園芸 , 1, スクール, 0 2, 園芸 , 3, レンタル, 1 3, 建設機器 , 2, 販売 , 0 3, 建設機器 , 3, レンタル, 0 1, アウトドアスポーツ, 2, 販売 , 1 1, アウトドアスポーツ, 1, スクール, 0
●ONとWHEREで違いが出る例とLEFT JOINとINNER JOINの違いを説明します。
(4)のON～の一部を変更して、次のようにWHEREを使った場合の例を示します。上記(4)と比較すると違いが分かります。
LEFT JOIN メンバーtoカテtoサブカテTB AS B
ON A.カテゴリID = B.カテゴリID AND A.サブカテゴリID = B.サブカテゴリID
WHERE メンバーID = '1'
[カテゴリID],[サブカテゴリID][カテゴリ名],[カテゴリソート],[サブカテゴリ名],[サブカテゴリソート],[メンバーID] 1, 2, アウトドアスポーツ, C, 販売 , a, 1 2, 3, 園芸 , A, レンタル, c, 1
WHEREでは結果テーブルがその制限を受けます。上記のように、その条件に当てはまらないレコードは残しません。
ONはあくまでも結合する際の基準です。ON～で指定された結合条件に当てはまらないものを残すかどうかは、LEFT JOINかINNER JOINかなどで決まります。
INNER JOINならば、結合条件に当てはまらないレコードは、結果テーブルに残しません。
LEFT JOINならば、左側に書いたテーブルのデータは、結合条件に当てはまらないレコードも残します。右側に書いたテーブルのデータは、該当するものがないので、代わりにNULLという値が入ります。
http://dev.mysql.com/doc/refman/4.1/ja/left-join-optimisation.ht...
マニュアルから引用です。（ちょっと変更しました。）
LEFT JOIN の条件は、右側のテーブルからのレコードの取り出し方法の判定に使用される。このとき、WHEREの条件は使用されない。
WHEREの条件にマッチするレコードがあり、右側のテーブルにON条件にマッチするレコードがない場合、左側のカラムの値が NULL に設定されたレコードが生成される。
●その他
SQL文を記述する際、テーブルが複数ある場合、同じフィールド名がある場合があります。どのテーブルのフィールドかを判定するために「テーブル名.フィールド名」という書き方にします。なお、FROM以降でテーブル名を指定する部分に「テーブル名 AS ～」と書くと、「～」をそのテーブル名の代わりに用いることができるようになります。
下記の２つは、同じ意味です。
USING( [フィールド名] )
ON [JOINの左側のテーブル名].[フィールド名] = [JOINの右側のテーブル名].[フィールド名]
No.1
913
30pt
[on]というのは、join～onのことと解釈します。
基本的にどちらの方法でも結合するということ自体に違いは
ありませんが、以下のURLを参考にしてください。
http://otn.oracle.co.jp/forum/thread.jspa?messageID=35004746
where句で書く場合は基本的にDBMSにより書き方が変わる場合がある
例）oracle：A.Code = B.Code(+)
sqlserver:A.COde += B.Code
など
Join～onを使う場合は標準としてどのDBMSでも同じ書き方で動作保証
されると認識しています。
DBMSに依存しないシステムにしたい場合は、Join～onを使用する
ほうが無難だと思われます。
SQL講座
なるほどです。
コメントにもいろいろご指導いただいていますが、私の根本的な勘違いで質問が理解しづらい文章になっていました＾＾；
> Join～onを使う場合は標準としてどのDBMSでも同じ書き方で動作保証されると認識しています。
このような回答は非常に勉強になります。
すなわち、処理的にもDBを変える場合などにもできるだけ抽出はFROM内で行ったほうがいいということですね＾＾
No.2
72254
15pt
ジョインの記述方法には、いくつか方法があります。
古くからあるのは、以下の書き方です。
select ～ from t1,t2 where t1.c1=t2.c1
その後、標準SQLなどで外部結合などと共に規定されたのが、以下の書き方です。
select ～ from t1 inner join t2 on t1.c1=t2.c1
これ以外にも、RDBMSによって独自の構文があります。
後者の書き方では、標準SQLでは、「onでは結合条件を、whereでは制限条件を書く」ことになっています。
結合条件は、表と表を結合するための条件であり、制限条件はそれ以外の条件を指します。
つまり、以下のような書き方になります。
select ～ from t1 inner join t2 on t1.c1=t2.c1 where t1.c1 between 100 and 200
なお、RDBMSの実装方法によっては、onで制限条件を書いても、エラーにせずに動く場合もあります。
標準SQLで答える必要のある情報処理技術者試験などでは、onとwhereを使い分ける必要があります。
No.3
72254
14pt
質問内容を誤解していたかも知れません。
ジョインに関しては、SQL92で規定されるまで、外部結合等に関しては標準仕様が存在しませんでした。
select ～ from t1,t2 where t1.c1=t2.c1
という記述が古くから存在しますが、これはSQL92で規定されたinner joinと等価です。
しかし、外部結合等に関しては、条件式に(+)を付けるなど、各RDBMSで、独自仕様により実装されてきました。
SQL92でようやく、以下のような外部結合等の構文が規定されました。
select ～ from t1 left join t2 on t1.c1=t2.c1 [where 制限条件]
多くのRDBMSは、標準SQLへの準拠を進めています。RDBMSにより、実装状況に差はありますが、標準SQLに準じたSQLなら、殆ど修正なしに複数のRDBMSで使用することが可能です。
なお、標準SQLでは、「onでは結合条件を、whereでは制限条件」を指定することになっています。RDBMSの実装では、「onで制限条件」を指定できるものもあるので、標準SQLで回答する必要がある情報処理技術者試験などでは注意が必要です。
No.4
23034
ここでベストアンサー
100pt
回答が大変長くなってしまいました。ご迷惑をお掛けしますが、よかったら、読んでください。
seadwellさんが既によく考えた例の方がわかりやすいと思いますので、前々回のご質問 http://q.hatena.ne.jp/1174771937 を具体例として、その質問のコメントに書いたＳＱＬを使って解説致します。※[カテゴリソート]フィールドと[サブカテゴリソート]フィールドを加え、それに伴ってＳＱＬを多少変更しました。※なお、LEFT JOIN～USINGを重ねる順番など、kurukuru-nekoさんから教え頂きました。
●カテゴリTB
[カテゴリID],[カテゴリ名],[カテゴリソート]
1, アウトドアスポーツ,C
2, 園芸 ,A
3, 建設機器 ,B
●サブカテゴリTB
[サブカテゴリID],[サブカテゴリ名],[サブカテゴリソート]
1, スクール , b
2, 販売 , a
3, レンタル , c
●カテtoサブカテTB
[カテゴリID],[サブカテゴリID]
1, 1
1, 2
2, 1
2, 3
3, 2
3, 3
●メンバーtoカテtoサブカテTB
[メンバーID],[カテゴリID],[サブカテゴリID]
1, 1, 2
1, 2, 3
2, 2, 1
2, 3, 2
●SQL文
SELECT A.カテゴリID, カテゴリ名, A.サブカテゴリID, サブカテゴリ名, IF(メンバーID IS NULL,0,1) AS 登録 FROM カテtoサブカテTB AS A LEFT JOIN カテゴリTB USING(カテゴリID) LEFT JOIN サブカテゴリTB USING(サブカテゴリID) LEFT JOIN メンバーtoカテtoサブカテTB AS B ON A.カテゴリID = B.カテゴリID AND A.サブカテゴリID = B.サブカテゴリID AND メンバーID = '1' ORDER BY カテゴリソート, サブカテゴリソート ;
●以下の(1)～(6)のように、SQLが実行され、結果テーブルが変化していくとイメージするとわかり易いと思います。
(1)FROM カテtoサブカテTB AS A
[カテゴリID],[サブカテゴリID] 1, 1 1, 2 2, 1 2, 3 3, 2 3, 3
(2)LEFT JOIN カテゴリTB USING(カテゴリID)
[カテゴリID],[サブカテゴリID][カテゴリ名],[カテゴリソート] 1, 1, アウトドアスポーツ, 3 1, 2, アウトドアスポーツ, 3 2, 1, 園芸 , 1 2, 3, 園芸 , 1 3, 2, 建設機器 , 2 3, 3, 建設機器 , 2
(3)LEFT JOIN サブカテゴリTB USING(サブカテゴリID)
[カテゴリID],[サブカテゴリID][カテゴリ名],[ソート],[サブカテゴリ名],[サブカテゴリソート] 1, 1, アウトドアスポーツ, C, スクール, b 1, 2, アウトドアスポーツ, C, 販売 , a 2, 1, 園芸 , A, スクール, b 2, 3, 園芸 , A, レンタル, c 3, 2, 建設機器 , B, 販売 , a 3, 3, 建設機器 , B, レンタル, c
(4)LEFT JOIN メンバーtoカテtoサブカテTB AS B
ON A.カテゴリID = B.カテゴリID AND A.サブカテゴリID = B.サブカテゴリID AND メンバーID = '1'
[カテゴリID],[サブカテゴリID][カテゴリ名],[カテゴリソート],[サブカテゴリ名],[サブカテゴリソート],[メンバーID] 1, 1, アウトドアスポーツ, C, スクール, b, NULL 1, 2, アウトドアスポーツ, C, 販売 , a, 1 2, 1, 園芸 , A, スクール, b, NULL 2, 3, 園芸 , A, レンタル, c, 1 3, 2, 建設機器 , B, 販売 , a, NULL 3, 3, 建設機器 , B, レンタル, c, NULL
(5)ORDER BY カテゴリソート, サブカテゴリソート
[カテゴリID],[サブカテゴリID][カテゴリ名],[カテゴリソート],[サブカテゴリ名],[サブカテゴリソート],[メンバーID] 2, 1, 園芸 , A, スクール, b, NULL 2, 3, 園芸 , A, レンタル, c, 1 3, 2, 建設機器 , B, 販売 , a, NULL 3, 3, 建設機器 , B, レンタル, c, NULL 1, 2, アウトドアスポーツ, C, 販売 , a, 1 1, 1, アウトドアスポーツ, C, スクール, b, NULL
(6)SELECT A.カテゴリID, カテゴリ名, A.サブカテゴリID, サブカテゴリ名, IF(メンバーID IS NULL,0,1) AS 登録
[カテゴリID],[カテゴリ名],[サブカテゴリID],[サブカテゴリ名],[登録] 2, 園芸 , 1, スクール, 0 2, 園芸 , 3, レンタル, 1 3, 建設機器 , 2, 販売 , 0 3, 建設機器 , 3, レンタル, 0 1, アウトドアスポーツ, 2, 販売 , 1 1, アウトドアスポーツ, 1, スクール, 0
●ONとWHEREで違いが出る例とLEFT JOINとINNER JOINの違いを説明します。
(4)のON～の一部を変更して、次のようにWHEREを使った場合の例を示します。上記(4)と比較すると違いが分かります。
LEFT JOIN メンバーtoカテtoサブカテTB AS B
ON A.カテゴリID = B.カテゴリID AND A.サブカテゴリID = B.サブカテゴリID
WHERE メンバーID = '1'
[カテゴリID],[サブカテゴリID][カテゴリ名],[カテゴリソート],[サブカテゴリ名],[サブカテゴリソート],[メンバーID] 1, 2, アウトドアスポーツ, C, 販売 , a, 1 2, 3, 園芸 , A, レンタル, c, 1
WHEREでは結果テーブルがその制限を受けます。上記のように、その条件に当てはまらないレコードは残しません。
ONはあくまでも結合する際の基準です。ON～で指定された結合条件に当てはまらないものを残すかどうかは、LEFT JOINかINNER JOINかなどで決まります。
INNER JOINならば、結合条件に当てはまらないレコードは、結果テーブルに残しません。
LEFT JOINならば、左側に書いたテーブルのデータは、結合条件に当てはまらないレコードも残します。右側に書いたテーブルのデータは、該当するものがないので、代わりにNULLという値が入ります。
http://dev.mysql.com/doc/refman/4.1/ja/left-join-optimisation.ht...
マニュアルから引用です。（ちょっと変更しました。）
LEFT JOIN の条件は、右側のテーブルからのレコードの取り出し方法の判定に使用される。このとき、WHEREの条件は使用されない。
WHEREの条件にマッチするレコードがあり、右側のテーブルにON条件にマッチするレコードがない場合、左側のカラムの値が NULL に設定されたレコードが生成される。
●その他
SQL文を記述する際、テーブルが複数ある場合、同じフィールド名がある場合があります。どのテーブルのフィールドかを判定するために「テーブル名.フィールド名」という書き方にします。なお、FROM以降でテーブル名を指定する部分に「テーブル名 AS ～」と書くと、「～」をそのテーブル名の代わりに用いることができるようになります。
下記の２つは、同じ意味です。
USING( [フィールド名] )
ON [JOINの左側のテーブル名].[フィールド名] = [JOINの右側のテーブル名].[フィールド名]
すごいです！バッチリです！
今、ハッキリと、確実に、確り、理解できました。
SQL文の組み立ての敷居が低くなった気がするので驚きです。
SQL書籍を何冊読むより、理解が早かったです。
テーブル設計も何が必要なのか逆算できそうです。訓練は必要でしょうが・・・
多分、リファレンスも少しは読みこなせそうな気がします。
多分これを理解している方はゴマンといるのでしょうが、人に教えるとなると別の話で、ここまでできるのはnandedarouさまならではと思います。
質問も回答もそうですが、人に文章で伝えるって非常に難しいのですよね。
質問の内容から何が知りたいのか？洞察し、労力をそこで半分以上使ってしまいます。
私の場合は話でもなかなか伝わりません＾＾；
相変わらずの名回答で、ただただ感服するばかりです。
No.5
72254
10pt
「RDBMSによっては、onで制限条件を書けるので、～」と回答中に書いてしまいましたが、nandedarouさんのコメントを見て勘違いと気付きました。
「onで書くものは結合条件」となるのでしたね。回答のその部分は訂正します。
違う角度からの回答をありがとうございます。
コメントまででもいろいろ教えていただき勉強になります。
tokyosmash
2007/03/30 08:33:54
94
91
kurukuru-neko
nandedarou
seadwell
chuken_kenkou
1
1
すごいです！バッチリです！
今、ハッキリと、確実に、確り、理解できました。
SQL文の組み立ての敷居が低くなった気がするので驚きです。
SQL書籍を何冊読むより、理解が早かったです。
テーブル設計も何が必要なのか逆算できそうです。訓練は必要でしょうが・・・
多分、リファレンスも少しは読みこなせそうな気がします。
多分これを理解している方はゴマンといるのでしょうが、人に教えるとなると別の話で、ここまでできるのはnandedarouさまならではと思います。
質問も回答もそうですが、人に文章で伝えるって非常に難しいのですよね。
質問の内容から何が知りたいのか？洞察し、労力をそこで半分以上使ってしまいます。
私の場合は話でもなかなか伝わりません＾＾；
相変わらずの名回答で、ただただ感服するばかりです。