SQL のいろんな結合

SQLで複数のテーブルからデータを取ってくる場合、いくつかの書きかたがあります。

たとえば、

SELECT * FROM SQLTEST/CUSTOMER C, SQLTEST/ORDER O 
WHERE C.CUSTNO = O.CUSTNO

SELECT * FROM SQLTEST/CUSTOMER C JOIN SQLTEST/ORDER O
ON C.CUSTNO = O.CUSTNO

はまったく同じ結果を返してくるわけですが、
ただ歴史的に書きかたが違う、というだけでなく発想が異なります。

また、後者の JOIN を使用する書きかたの方が確かに歴史的にも新しく、それまでになかった「FROM 句に対して結合されたデータの集合を指定できる」という機能を導入したわけですが、
この「結合」=JOIN の仕方にはいくつかの種類があります。
この「結合」の仕方の区別がまた、得てして混乱の元になっているので、そのあたりも含めて複数テーブルをSQL で扱う時のひとつの考え方を簡単にまとめてみたいと思います。

以下のようなテーブルを材料に使います。

CUSTOMER と

ORDER です。

よくありそうなかんじですよね。

中身はそれぞれこんなかんじです。


一致するデータだけの結合(内部結合)

顧客番号が一致しているデータを取得したい場合を考えます。

まず、書きかたその1。

SELECT * FROM SQLTEST/CUSTOMER C, SQLTEST/ORDER O
WHERE C.CUSTNO = O.CUSTNO

JOIN キーワードを使っても同じ結果を得ることができます。(書きかたその2)

SELECT * FROM SQLTEST/CUSTOMER C JOIN SQLTEST/ORDER O
ON C.CUSTNO = O.CUSTNO

このただ JOIN としただけの書きかたは、実は INNER JOIN というかたちの省略形になります。
省略しないで書くと↓のようになりますね。

SELECT * FROM SQLTEST/CUSTOMER C INNER JOIN SQLTEST/ORDER O
ON C.CUSTNO = O.CUSTNO

「書き方その1」と「書き方その2」との違いは?

最初の「書きかたその1」から WHERE 句をはずしてみましょう。

SELECT * FROM SQLTEST/CUSTOMER C, SQLTEST/ORDER O

どういう組み合わせかわかりますか?

FROM 句に対し、まずひとつめに指定されたテーブルの 1行めに対して、ふたつめに指定されたテーブルの全行、次にひとつめのテーブルの 2行めに対してふたつめのテーブルの全行、といった具合になっています。つまり、総当りの、あり得る組み合わせ全部、ということですね。
ちなみにこの組み合わせかた(= 結合のしかた)は「クロス・ジョイン」とか「直積」、「デカルト積」といわれます。

これは「プログラマのためのSQL」の第17章「SELECT文」p.206 に↓のように書いてありますが、SQL の想定動作としては正しい動きです。

「FROM 句を実行して、その句で定義された作業用の結果テーブルを生成します。」
「結果は、全テーブル内の全行のクロス結合になります。最初のテーブルから各行を持ってきて、それに2番目のテーブルの各行を連結し、結果テーブルの新しい行を構成します。これで、可能性のあるすべての結合が得られます。」
「実際には、どの製品も中間テーブル作成にクロス結合を使っていません。作業用のテーブルはすぐに大きくなり過ぎる可能性があります。」

先の「書きかたその1」を改めて見てみてください。

SELECT * FROM SQLTEST/CUSTOMER C, SQLTEST/ORDER O

WHERE C.CUSTNO = O.CUSTNO

という条件を加えた形になっているわけですね。

つまり、「クロス・ジョイン」した 2つのテーブルの集合、総当りしたデータの集合から顧客番号の一致したものを抜き出せ、という発想なわけです。

「書きかたその2」を見てみましょう。

SELECT * FROM SQLTEST/CUSTOMER C JOIN SQLTEST/ORDER O  
ON C.CUSTNO = O.CUSTNO

カッコでくくってみるとわかりやすくなるでしょうか。

SELECT * FROM ( SQLTEST/CUSTOMER C JOIN SQLTEST/ORDER O
ON C.CUSTNO = O.CUSTNO )

FROM 句に "テーブルとテーブルをある条件で「結合」させた新たな集合" を指定できるように拡張した、ということなんですね。
2つのテーブルのうち、顧客番号の一致している行を新たにひとつの行とした、新たな集合を定義し、それをFROM 句に指定できるようになった、ということです。

後でいくつか例をあげますが、このことにより集合を個々のテーブルにとらわれずに操作できるようになり、SQL をよりわかりやすいものにすることができています。
(複雑な条件節を記述しなくてもよくなり、よりヴェン図的な発想で記述できるようになっている、ということです)

「書きかたその2」で記述した INNER JOIN は2つのテーブルで共通するもの、を抽出していることがわかりますね。
つまり、

から

が出てきているわけです。
顧客番号 00006 の佐藤さんは ORDER テーブルに一致するオーダー(行)がないため、ここでは表示されていません。

当然いろいろな組み合わせが考えられるわけで、そのひとつが先ほど例に挙げた、一致も何も関係なく全ての組み合わせを出力する「クロス結合(CROSS JOIN)」になります。

「書きかたその2」は一致しているもののみを抽出しているので「内部結合(INNER JOIN)」といいます。
ヴェン図の真中ですね。


一致するものがなかったとしてもそれを表示させたい(外部結合)

一致するものがないケースも集合に含む場合を「外部結合(OUTER JOIN)」といいます。
JOIN という文字をはさんで右にあるテーブルを「右」、左にあるテーブルを「左」とし、そのどちら側に存在するかしないか、で外部結合にはさらにいくつかの種類があるわけです。

左外部結合(LEFT OUTER JOIN)

「左」にあるテーブルをすべて生かし、「右」に一致する行がない場合でも抽出対象とする、というのが「左外部結合(LEFT OUTER JOIN)」と呼ばれる結合形態になります。

SELECT * FROM SQLTEST/CUSTOMER C LEFT OUTER JOIN SQLTEST/ORDER O 
ON C.CUSTNO = O.CUSTNO

先ほど例に挙げた、ORDER テーブルに一致する行がない佐藤さんが今回は出力されているのがわかりますね。

右外部結合(RIGHT OUTER JOIN)

「左外部結合(LEFT OUTER JOIN)」とは逆に「右」のテーブルをすべて保持するのが「右外部結合(RIGHT OUTER JOIN)」になります。

SELECT * FROM SQLTEST/CUSTOMER C RIGHT OUTER JOIN SQLTEST/ORDER O 
ON C.CUSTNO = O.CUSTNO                                             

「右」に顧客番号で一致する行がない佐藤さんはやはり消えてしまいますが、「左」に一致するものがない(オーダーは存在するが顧客番号がマスターにない)行である A0005 が出現しているのがわかりますね。

例外結合(EXCEPTION JOIN)

一致していないものだけを抽出する「例外結合(EXCEPTION JOIN)」というものもあります。
チェックしたいときなんかに実は意外と使い道があります。

先ほどの、「左」にあって「右」には指定されたキーと一致する行だけを抽出するものを「左例外結合(LEFT EXCEPTION JOIN)」と呼びます。

SELECT * FROM SQLTEST/CUSTOMER C LEFT EXCEPTION JOIN SQLTEST/ORDER
O ON O.CUSTNO = C.CUSTNO

"佐藤さん"だけが出力されるわけですね。

結合を使用したからといって WHERE句による条件指定ができなくなるわけではありません。
「左外部結合(LEFT OUTER JOIN)」から、「右」に一致するキーが存在していない=つまりNULL になっているものを検索してやれば結果は同じになります。

SELECT * FROM SQLTEST/CUSTOMER C LEFT OUTER JOIN SQLTEST/ORDER O 
ON C.CUSTNO = O.CUSTNO                                           
WHERE O.CUSTNO IS NULL

右外結合(RIGHT EXCEPTION JOIN)

逆の、「右」にあって「左」には一致する行がないものを抽出するのは「右外結合(RIGHT EXCEPTION JOIN)」と呼ばれます。

SELECT * FROM SQLTEST/CUSTOMER C RIGHT EXCEPTION JOIN SQLTEST/ORDER  
O ON C.CUSTNO = O.CUSTNO

これについても、↓のように書いてみても同じ結果が出てきます。

SELECT * FROM SQLTEST/CUSTOMER C RIGHT OUTER JOIN SQLTEST/ORDER O 
ON C.CUSTNO = O.CUSTNO                                            
WHERE C.CUSTNO IS NULL

「右」「左」はどこの右・左?

「右」「左」ですが、JOIN キーワードのすぐ次にくるデータ集合(テーブル)の定義を指します。
ON キーワードに続く条件にも確かに「右」「左」はありますが、関係ありません。

LEFT OUTER JOIN T1 と書かれていれば、「右」はT1 です。全ての行が残されるのはT1 でない方のテーブル(もしくはデータ集合)です。ON の次の条件、たとえば = という文字の位置関係は関係ありません。

試しにON の次にある O と C を入れ替えてみましょう。

SELECT * FROM SQLTEST/CUSTOMER C LEFT OUTER JOIN SQLTEST/ORDER O 
ON O.CUSTNO = C.CUSTNO

結果は変わりませんね。

逆に JOIN キーワードの「右」側のテーブルを変更してみましょう。

SELECT * FROM SQLTEST/ORDER O LEFT OUTER JOIN SQLTEST/CUSTOMER C 
ON C.CUSTNO = O.CUSTNO

結果も変わってしまっていることがわかりますね。

読みやすくするためのカッコ

先に、カッコでくくるとくくりがわかりやすくなる、と言いましたが、実際にカッコでくくっても実行できます。

SELECT * FROM ( SQLTEST/CUSTOMER C JOIN SQLTEST/ORDER O 
ON C.CUSTNO = O.CUSTNO )

カッコでくくった効果を見るために、三つ以上のテーブルを使用してみましょうか。

テーブルをひとつ増やします。

カッコでくくったデータ集合に対してもさらに結合を行うことができます。

SELECT * FROM ( SQLTEST/CUSTOMER C JOIN SQLTEST/ORDER O  
ON C.CUSTNO = O.CUSTNO ) LEFT OUTER JOIN SQLTEST/SALES S 
ON O.ORDNO = S.ORDNO

もちろん「書きかたその1」でのように、カッコでくくったデータ集合と追加のテーブル(もちろん"カッコでくくったデータ集合"でもOKです)のクロス結合に対するWHEREでの条件選択、という書きかたも可能です。

SELECT * FROM ( SQLTEST/CUSTOMER C JOIN SQLTEST/ORDER O  
ON C.CUSTNO = O.CUSTNO ), SQLTEST/SALES S                
WHERE O.ORDNO = S.ORDNO

ただし、ここにさらに条件を指定しようとするとちょっと煩雑なSQL になります。
JOIN での結合指定とWHEREでの条件指定が混乱している印象がありますね。

SELECT * FROM ( SQLTEST/CUSTOMER C JOIN SQLTEST/ORDER O 
ON C.CUSTNO = O.CUSTNO ), SQLTEST/SALES S               
WHERE O.ORDNO = S.ORDNO                                 
  AND S.SALES < 100000

"カッコでくくったデータ集合に対してさらに結合"したデータ集合についてもWHERE句での条件指定はもちろんできます。

やはり、FROM 句にJOIN を使用したデータ集合の定義、WHERE句ではそのデータ集合のデータに対する条件の指定、というように整理した方がわかりやすいと思います。
WHERE句は純粋に条件の指定のために使用するように意識しましょう。

SELECT * FROM ( SQLTEST/CUSTOMER C JOIN SQLTEST/ORDER O  
ON C.CUSTNO = O.CUSTNO ) LEFT OUTER JOIN SQLTEST/SALES S 
ON O.ORDNO = S.ORDNO                                     
WHERE S.SALES < 100000

このようにしておけば、カッコの中を変えるだけで結果となる集合は変えられます。

SALES テーブルに対する「左」の内容はカッコの中を変えるだけで簡単に変えられるわけですね。
カッコで検索対象となるデータ集合の内容をまとめておけるので、変更がわかりやすくなると思います。

SELECT * FROM ( SQLTEST/CUSTOMER C RIGHT OUTER JOIN SQLTEST/ORDER O 
ON C.CUSTNO = O.CUSTNO ) LEFT OUTER JOIN SQLTEST/SALES S            
ON O.ORDNO = S.ORDNO

FROM 句にはデータの集合を定義する、ということは理解していただけましたか?

極端に言うと、↓のようなことすら可能なわけです。

select * from (                                         
   SELECT * FROM ( SQLTEST/CUSTOMER C JOIN SQLTEST/ORDER O ON C.CUSTNO = O.CUSTNO ), SQLTEST/SALES S               
                      WHERE O.ORDNO = S.ORDNO                                 
                        AND S.SALES < 100000                                  
               ) as x 

このデータ集合(SELECT文の結果、ですね)にたいしての JOIN ももちろん可能です。

USING キーワード

ついでですが、カラム名が同一であれば USING を使用することも可能です。

[Top Pageに戻る]

Ads by TOK2