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

第1回 さまざまな結合
Oracle SQLの各機能をイメージ図を交えて解説

Oracle ACE
山岸 賢治(やまぎし けんじ)ACE

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のイメージは下記です。
内部結合のベン図と、紐づくデータを取得する流れをイメージしています。

▲ ページTOPに戻る

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ではない行を消去するグレー線をイメージしています。

▲ ページTOPに戻る

外部結合

キーに紐づくデータを取得するが、結合元の行は必ず表示

外部結合の典型的な使用例は、下記のように、キーに紐づくデータを取得するが、キーに紐づくデータが存在しなかったとしても、結合元の行は表示させるケースです。

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のイメージは下記です。
外部結合のベン図と、紐づくデータを取得する流れをイメージしています。

▲ ページTOPに戻る

完全外部結合

キーに紐づくデータを取得するが、両方のテーブルの行を必ず表示

完全外部結合の典型的な使用例は、下記のように、キーに紐づくデータを取得し、かつキーに紐づくデータが存在しなかったとしても、両方のテーブルの行を表示させるケースです。
完全外部結合は、似たような定義のテーブル同士でよく使われます。

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のイメージと似ています。

▲ ページTOPに戻る

クロスジョイン

行の組み合わせを列挙

クロスジョインの典型的な使用例は、下記のように、行の組み合わせを列挙したいケースです。
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行ごとに結合先の全行が紐づく様子をイメージしています。

▲ ページTOPに戻る

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される様子をイメージしています。

▲ ページTOPに戻る

自己結合

同じテーブル同士で、行の組み合わせを列挙

自己結合の典型的な使用例は、下記のように、同じテーブル同士で、行の組み合わせを列挙するケースです。

なお、同じテーブルとの結合であれば自己結合と呼ばれます。

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

▲ ページTOPに戻る

参考リソース

▲ ページTOPに戻る

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

Copyright © 2010, Oracle Corporation Japan. All rights reserved.
無断転載を禁ず

この文書はあくまでも参考資料であり、掲載されている情報は予告なしに変更されることがあります。日本オラクル社は本書の内容に関していかなる保証もいたしません。また、本書の内容に関連したいかなる損害についても責任を負いかねます。

Oracleは米国Oracle Corporationの登録商標です。文中に参照されている各製品名及びサービス名は米国Oracle Corporationの商標または登録商標です。その他の製品名及びサービス名はそれぞれの所有者の商標または登録商標の可能性があります。

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



Left Curve
図でイメージする
Oracle DatabaseのSQL全集
Right Curve