図でイメージするOracle DatabaseのSQL全集 indexページ▶▶
SQLの初心者から上級者までを広く対象読者として、Oracle SQLの各機能の典型的な使用例を、学習効率が高いと思われる順序で、SQLのイメージ図を交えて解説します。
SQLをイメージつきで理解することで、素早くイメージからSQLを考えられるようになることを目標とします。
本連載の第1回目では、SQLでの「結合」についてとりあげます。
以下の図は、私がまとめたSelect文の評価順序ですが、今回は1番目のfrom句、2番目と5番目であるwhere句に関連したSQL機能である、さまざまな「結合」の典型的な使用例と、SQLのイメージを解説します。
1番目 | from句 |
2番目 | where句 (結合条件) |
3番目 | start with句 |
4番目 | connect by句 |
5番目 | where句 (行のフィルタ条件) |
6番目 | group by句 |
7番目 | having句 |
8番目 | model句 |
9番目 | select句 |
10番目 | union、minus、intersectなどの集合演算 |
11番目 | order by句 |
Oracle Database 11g Release 11.2.0.1.0 (windows 32ビット版)
内部結合の典型的な使用例は、下記のように、キーに紐づくデータを取得するケースです。
結合条件が、値が等しいことである場合は、等価結合とも呼ばれます。
Oracle8i Databaseまでは、where句に結合条件を記述する必要がありましたが、Oracle9i Databaseからは、SQL99構文のJoinが使えます。
create table oyaTable(ID primary key,Val) as select 111,100 from dual union all select 222,200 from dual union all select 333,300 from dual union all select 444,400 from dual; create table koTable(ID,seq, primary key(ID,seq)) as select 111,1 from dual union all select 111,2 from dual union all select 111,3 from dual union all select 333,1 from dual union all select 444,1 from dual union all select 444,5 from dual;
-- 内部結合を使ったSQL1 (where句に結合条件を記述) select a.ID,a.Val,b.seq from oyaTable a,koTable b where a.ID=b.ID order by a.ID,b.seq; -- 内部結合を使ったSQL2 (Joinを使用) select a.ID,a.Val,b.seq from oyaTable a Join koTable b on a.ID=b.ID order by a.ID,b.seq;
出力結果 ID Val seq --- --- --- 111 100 1 111 100 2 111 100 3 333 300 1 444 400 1 444 400 5
内部結合のSQLのイメージは下記です。
内部結合のベン図と、紐づくデータを取得する流れをイメージしています。
where句の典型的な使用例は、下記のように、必要な行を抽出するケースです。
create table filterSample(ID,Val) as select 1,60 from dual union all select 2,20 from dual union all select 3,60 from dual union all select 4,50 from dual union all select 5,60 from dual;
Valが60の行を抽出します。
-- where句を使ったSQL select ID,Val from filterSample where Val=60;
出力結果 ID Val -- --- 1 60 3 60 5 60
where句のSQLのイメージは下記です。
where句での論理演算の結果が、Trueではない行を消去するグレー線をイメージしています。
外部結合の典型的な使用例は、下記のように、キーに紐づくデータを取得するが、キーに紐づくデータが存在しなかったとしても、結合元の行は表示させるケースです。
Oracle8i Databaseまでは、結合演算子(+)を使う必要がありましたが、Oracle9i Databaseからは、SQL99構文のLeft Joinが使えます。
create table oyaTable2(ID primary key,Val) as select 111,100 from dual union all select 222,200 from dual union all select 333,300 from dual; create table koTable2(ID,seq, primary key(ID,seq)) as select 111,1 from dual union all select 111,2 from dual union all select 222,1 from dual union all select 444,3 from dual;
-- 外部結合を使ったSQL1 (結合演算子(+)を使用) select a.ID,a.Val,b.seq from oyaTable2 a,koTable2 b where a.ID = b.ID(+) order by a.ID,b.seq; -- 外部結合を使ったSQL2 (Left Joinを使用) select a.ID,a.Val,b.seq from oyaTable2 a Left Join koTable2 b on a.ID = b.ID order by a.ID,b.seq;
出力結果 ID Val seq --- --- ---- 111 100 1 111 100 2 222 200 1 333 300 null
外部結合のSQLのイメージは下記です。
外部結合のベン図と、紐づくデータを取得する流れをイメージしています。
完全外部結合の典型的な使用例は、下記のように、キーに紐づくデータを取得し、かつキーに紐づくデータが存在しなかったとしても、両方のテーブルの行を表示させるケースです。
完全外部結合は、似たような定義のテーブル同士でよく使われます。
create table FullJoinT1(ID primary key,Val) as select 111,100 from dual union all select 222,200 from dual union all select 555,300 from dual; create table FullJoinT2(ID primary key,Val) as select 111,400 from dual union all select 222,500 from dual union all select 666,600 from dual;
-- 完全外部結合を使ったSQL select a.ID as a_ID,b.ID as b_ID, a.Val as a_Val,b.Val as b_Val from FullJoinT1 a full Join FullJoinT2 b on a.ID = b.ID order by a.ID,b.ID;
出力結果 a_ID b_ID a_Val b_Val ---- ---- ----- ----- 111 111 100 400 222 222 200 500 555 null 300 null null 666 null 600
完全外部結合のSQLのイメージは下記です。
完全外部結合のベン図と、紐づくデータを取得する流れをイメージしています。
完全外部結合のイメージは、集合演算のunionのイメージと似ています。
クロスジョインの典型的な使用例は、下記のように、行の組み合わせを列挙したいケースです。
Oracle9i Databaseからは、SQL99構文のCross Joinが使えます。
create table BaseT(ID primary key) as select 111 from dual union all select 222 from dual union all select 333 from dual; create table numT(Seq) as select 1 from dual union all select 2 from dual union all select 3 from dual;
-- クロスジョインを使ったSQL1 (SQL99構文を使用せず) select a.ID,b.seq from BaseT a,numT b order by a.ID,b.seq; -- クロスジョインを使ったSQL2 (SQL99構文を使用) select a.ID,b.seq from BaseT a Cross Join numT b order by a.ID,b.seq;
出力結果 ID seq --- --- 111 1 111 2 111 3 222 1 222 2 222 3 333 1 333 2 333 3
クロスジョインのSQLのイメージは下記です。
結合元の1行ごとに結合先の全行が紐づく様子をイメージしています。
Oracle10g Databaseから、Partitioned Outer Joinという新機能が追加されました。
パーティション化された外部結合とも呼ばれます。
Partitioned Outer Joinを使うと、パーティションを切ってできた、それぞれの集合と外部結合させることができます。
Partitioned Outer Joinの典型的な使用例は、下記のように、集合ごとにマスタ側に存在しなければ行を補完するケースです。
create table LPOTable(ID,Key,Name) as select 111,10,'aaaa' from dual union all select 111,20,'bbbb' from dual union all select 222,20,'cccc' from dual union all select 222,30,'dddd' from dual union all select 333,10,'eeee' from dual; create table MasterT(hokanKey) as select 10 from dual union all select 20 from dual union all select 30 from dual;
LPOTableのIDごとに、MasterTの行がなければ、Nameをnullとして補完します。
-- Partitioned Outer Joinを使ったSQL select b.ID,a.hokanKey,b.Name from MasterT a Left Join LPOTable b partition by (b.ID) on a.hokanKey = b.Key order by b.ID,a.hokanKey;
出力結果 ID hokanKey Name --- -------- ---- 111 10 aaaa 111 20 bbbb 111 30 null 222 10 null 222 20 cccc 222 30 dddd 333 10 eeee 333 20 null 333 30 null
Partitioned Outer JoinのSQLのイメージは下記です。
partition by (b.ID)で、IDごとに区切る赤線を引いて、外部結合のベン図と、紐づくデータを取得する流れをイメージし、それぞれの外部結合の結果がunion allされる様子をイメージしています。
自己結合の典型的な使用例は、下記のように、同じテーブル同士で、行の組み合わせを列挙するケースです。
なお、同じテーブルとの結合であれば自己結合と呼ばれます。
create table SelfJoinT(ID) as select 111 from dual union all select 222 from dual union all select 333 from dual union all select 444 from dual;
-- 自己結合を使ったSQL select a.ID as a_ID,b.ID as b_ID from SelfJoinT a,SelfJoinT b where a.ID < b.ID;
出力結果 a_ID b_ID ---- ---- 111 222 111 333 111 444 222 333 222 444 333 444
自己結合のSQLのイメージは下記です。
自己結合なので、テーブルのコピーをイメージし、この場合は内部結合でもあるので、紐づくデータを取得する流れをイメージしています。
"図でイメージするOracle DatabaseのSQL全集" インデックスに戻る
Copyright © 2010, Oracle Corporation Japan. All rights reserved.
無断転載を禁ず |
この文書はあくまでも参考資料であり、掲載されている情報は予告なしに変更されることがあります。日本オラクル社は本書の内容に関していかなる保証もいたしません。また、本書の内容に関連したいかなる損害についても責任を負いかねます。 Oracleは米国Oracle Corporationの登録商標です。文中に参照されている各製品名及びサービス名は米国Oracle Corporationの商標または登録商標です。その他の製品名及びサービス名はそれぞれの所有者の商標または登録商標の可能性があります。 |
山岸 賢治(やまぎし けんじ)
Oracle ACEの1人。
OracleSQLパズルの運営者。