図でイメージするOracle DatabaseのSQL全集　第1回 さまざまな結合

図でイメージするOracle DatabaseのSQL全集　indexページ▶▶

Oracle SQLの各機能をイメージ図を交えて解説

Oracle ACE
山岸 賢治（やまぎし けんじ）

SQLの初心者から上級者までを広く対象読者として、Oracle SQLの各機能の典型的な使用例を、学習効率が高いと思われる順序で、SQLのイメージ図を交えて解説します。
SQLをイメージつきで理解することで、素早くイメージからSQLを考えられるようになることを目標とします。

目次

• はじめに
• 内部結合
• where句
• 外部結合
• 完全外部結合
• クロスジョイン
• Partitioned Outer Join
• 自己結合

はじめに

本連載の第１回目では、SQLでの「結合」についてとりあげます。
以下の図は、私がまとめたSelect文の評価順序ですが、今回は1番目のfrom句、2番目と5番目であるwhere句に関連したSQL機能である、さまざまな「結合」の典型的な使用例と、SQLのイメージを解説します。

動作確認環境

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句

必要な行を抽出

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行ごとに結合先の全行が紐づく様子をイメージしています。

Partitioned Outer Join

パーティションを切ってできた、それぞれの集合と外部結合

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のイメージは下記です。
自己結合なので、テーブルのコピーをイメージし、この場合は内部結合でもあるので、紐づくデータを取得する流れをイメージしています。

参考リソース

• マニュアル --- 結合
• SQLアタマアカデミー：第10回　結合大全

"図でイメージするOracle DatabaseのSQL全集" インデックスに戻る

　山岸 賢治（やまぎし けんじ）
　Oracle ACEの1人。
　OracleSQLパズルの運営者。

ページトップへ戻る▲

図でイメージするOracle DatabaseのSQL全集　indexページ▶▶