JOIN(結合)

SYNTAX SELECT field FROM tblA[, ...] DESC 2つ以上のテーブルをひとつに結合して結果をかえす。 単純結合(simple join)と内部結合(inner join)は同じ
-- こんな意味 SELECT column FROM テーブル 結合方式 結合するテーブル 結合条件 -- テーブルを複数指定して ON 句の後に結合条件をかく select column from tblA join tblB on ...
おおまかに3つの結合方式がある
(.) 内部結合 (.) 両方のテーブルに値が存在するデータを取得する (.) 外部結合 (.) 両方のテーブルに値が一致しない場合もデータとして取得する。 (.) 左側のテーブルにしかデータが無い行も含めてデータを取得する (.) 右側のテーブルにしかデータが無い行も含めてデータを取得する (.) 交差結合 (.) 両側のテーブルのレコードのすべての組み合わせを返す。
結合方法に関係なく結合条件を2つから選べる。
(.) 等価結合 // 両方のテーブルに等しい値が存在する場合の条件 (ON A = B) (.) 非等価結合 // 両方のテーブルに等しい値が存在しないが関連する値が存在する場合の条件(ON A BETWEEN 1 AND 100)
完全に一致した場合のみ INNER JOIN をするには次のようにする。
select member.name, pref.adr from member INNER JOIN pref ON member.idadr = adr.id;
範囲内で一致した場合に INNER JOIN する。
select member.name, pref.adr from member INNER JOIN pref ON member.idadr ;
結合条件のカラム名がテーブル間で同じなら ON の代わりに USING を使って簡単にかける。
-- on man.name = woman.name と同じ select man.name, woman.name from man INNER JOIN woman USING name;



内部結合(INNER JOIN)

SYNTAX tblref, tblref tblref [INNER ]JOIN tblref [join_condition] DESC 内部結合とは主キーと外部キーを比較して一致するカラムのみを返す結合方法のこと。 参照して組み込むから INNER JOIN という ON句, USING句でテーブルの結合条件を指定する。 POINT 次の構文はすべて結果としては同じ。
-- INNER JOIN または [,] で 内部結合をする select mem.name, adr.pref from mem, adr where mem.idadr = adr.id; select mem.name, adr.pref from mem JOIN adr on mem.idadr = adr.id; -- INNER を明示しても良い select mem.name, adr.pref from mem INNER JOIN adr on mem.idadr = adr.id; WARNING -- , の場合は ON は使えない。where を使うこと。 select mem.name, adr.pref from mem, adr where mem.idadr = adr.id; WARNING -- 結合条件を指定しないと 外部結合による外積の結果になる。 select mem.name, adr.pref from mem, adr; select mem.name, adr.pref from mem INNER JOIN adr;
// 組み込む前 ( 3 が別テーブルへの ID ) [ A | B | 3 | D ] // 参照先テーブル [ 3 | "あ" | "い" | "う" ] // 組み込み後 [ A | B | "あ" | "い" | "う" | D ]
// ON のかわりに WHERE を使っても良い SELECT field FROM tblA INNER JOIN tblB WHERE tblA.x = tblB.y // 結合した Table から SELECT field FROM tblA INNER JOIN tblB WHERE tblA.x = tblB.y // さらに絞り込む and tblA.y = 10;



外部結合(OUTER JOIN)

SYNTAX tblref LEFT [OUTER] JOIN tblref [join_condition] tblref RIGHT [OUTER] JOIN tblref [join_condition] POINT 内部結合とは異なり、カラムが一致しない場合も左( または右 )側のテーブルの 全レコードを出力する結合方式。
-- member テーブルを左側にして, 右にアドレステーブルを結合する。 -- 外部結合のキーワードとして LEFT をつけること。 select mem.name, adr.pref from mem LEFT JOIN adr on mem.idadr = adr.id; -- LEFT キーワードを付ければ外部結合になるため OUTER キーワードは任意。 -- 結果は同じ。 select mem.name, adr.pref from mem LEFT OUTER JOIN adr on mem.idadr = adr.id;
POINT LEFT, RIGHT の指定は基準となるテーブルを指定する時に使う。 RIGHT は LEFT の別表現のため、覚えるのがメンドイならば LEFT だけを使えばいい。 またDBの移植性を考えると LEFT に統一すること。
-- memer テーブルはレフト, adr テーブルはライト扱いになる。 -- 右扱いのテーブルは一致するカラムが無い場合は NULL が埋められる。 -- 左扱いのテーブルは必ずデータが取得される。 select mem.name, adr.pref from mem left join adr on mem.idadr = adr.id; -- これは同じ結果になる。 left <-> right を変更してテーブルの指定の指定を切り替えただけ。 -- 基準は memer テーブルのまま。 select mem.name, adr.pref from adr right join mem on mem.idadr = adr.id; -- これは逆になる。 select mem.name, adr.pref from mem right join adr on mem.idadr = adr.id;



交差結合(CROSS JOIN)

DESC テーブル1の各レコードに対してテーブル2のすべてレコードを組み合わせた結合方式のこと。 結果は テーブル1のレコード数 * テーブル2のレコード数となる。 内部結合、外部結合のように結合条件は必要ない。
select member.name, adr.name from member CROSS JOIN adr



自己結合

DESC 結合対象のテーブルが自分自身のこと。 結合方式は内部結合と外部結合と同じ。 結合対象のテーブルが同じなのでテーブルのエイリアスは必須。
select m.name, m2.name from member as m JOIN member as m2 on m.parent = m2.id;



STRAIGHT_JOIN

SYNTAX tblref STRAIGHT_JOIN tblref


NATURAL_JOIN

SYNTAX tblref NATURAL [LEFT [OUTER]] JOIN tblref { OJ tblref LEFT OUTER JOIN tblref ON conditional_expr } tblref NATURAL [RIGHT [OUTER]] JOIN tblref DESC 結合する両テーブルの一致カラムをすべて USING 句に指定した場合と同じ。


単純結合

SYNTAX select field ... from tbl [, tbl...] DESC 総当たり戦 みたいなものだと 思えばOK 数学でいうと 直積 です ( めったに使いません ) 単純結合(simple join)と内部結合(inner join)はおなじ 受注m の各レコードに 品番m の全レコードが結合される 受注m のレコード数 x 品番m のレコード数の結果が問い合わされる
select * from 受注m, 品番m; select * from 受注m join 品番m;



等価結合

DESC Image でいうと 条件のあった ROW を 結合するような感じ 特定のキーで表を結合する 条件のどちらかのデータが存在しないとき、結果には含まれない ( ERROR にはならない ) 外部結合が一番使いやすい select // field 受注番号, 受注m.品番 品番, 品番m.入庫数 入庫数 // table from sak.受注m, sak.品番m // ここが 等価になるところ // 2 つの table の 品番が同じ ENTRY をひっつける where 受注m.品番 = 品番m.品番 TIP 結合テーブルに同じ Column 名があるときは, table 名をつけることで一意に指定します
// tblA の name と tblB の name を参照する select tblA.name, tblB.name from tblA, tblB where ...
列の別名定義は、as( alias ) を指定できる
select // Column の指定 受注番号, 受注m.品番 as 品番, 品番m.入庫数 as 入庫数 // table の指定 ( ここでは 2 つを参照する ) from sak.受注m, sak.品番m // 条件 で table 間を LINK します where 受注m.品番 = 品番m.品番 ;
// 名前が重複したときに, 項目名を かえるときに使えばいいだけ select tbl.NAME as tako, tbl.COMMAND, foo.nr from tbl, foo where tbly.NAME = foo.txt and foo.ID = 15;
select // Column 受注番号, 受注m.品番 as 品番, 品番m.入庫数 as 入庫数 // Table from sak.受注m // ここでは inner を明示する inner join sak.品番m on 受注m.品番 = 品番m.品番 ;
Table 間の条件だけではなく, Column の条件もかけます
select tbl.NAME, tblB.nr from tbl inner join tblB on tbl.NAME = foo.txt select tbl.NAME, tblB.nr from tbl inner join tblB on tbl.NAME = foo.txt;
WARNING 結合カラムになっている所属IDカラムの値です 社員テーブルの所属IDカラムには「1,2,3,NULL」の4つの値があるが 所属テーブルの所属IDカラムは「1,3,4」の3つの値のみ 社員テーブルにある「2」や「NULL」は所属テーブルになく 所属テーブルにある「4」は社員テーブルにありません Join( 結合方法 ) は 2種類あります
(.) InnerJoin(内部結合)<- Default はこちら (.) OuterJoin(外部結合)
// Default は InnerJoin // 結合先がないと SKIP されます select * tblA, tblB // InnerJoin であることを明示する select * tblA inner join tblB on tblA.id = tblB.id // 結合先がなくても, 結果をかえすなら OuterJoin をつかう
3 つ以上の結合を inner join で指定
select * from (sak.受注m inner join sak.品番m on 受注m.品番 = 品番m.品番) inner join sak.得意先m on 受注m.得意先CD = 得意先m.得意先CD;



Columnを計算した結果をかえす