図でイメージするOracleのSQL全集の元原稿
第2回 集合演算など
第1回 内部結合や外部結合など
連載のテーマ
SQLの初心者から上級者までを広く対象読者として、
SQLの各機能の典型的な使用例を、学習効率が高いと思われる順序で、SQLのイメージを交えて解説します。
SQLをイメージ付で理解し、SQLをイメージで素早く考えられるようになることを目標とします。
今回のテーマ
今回は、下記のOracleのSQL文の評価順序においての、
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ビット版)
目次
1 内部結合
結合条件を満たすデータを取得
内部結合の典型的な使用例は、下記のように、キーに紐づくデータを取得するケースです。
結合条件が、値が等しいことである場合は、等価結合とも呼ばれます。
Oracle8iまでは、where句に結合条件を記述する必要がありましたが、
Oracle9iからは、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のイメージは下記です。
内部結合のベン図と、紐づくデータを取得する流れをイメージしてます。
2 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でない行を、消去するグレー線をイメージしてます。
3 外部結合
キーに紐づくデータを取得するが、結合元の行は必ず表示
外部結合の典型的な使用例は、下記のように、キーに紐づくデータを取得するが、
キーに紐づくデータが存在しなかったとしても、結合元の行は表示させるケースです。
Oracle8iまでは、結合演算子(+)を使う必要がありましたが、
Oracle9iからは、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のイメージは下記です。
外部結合のベン図と、紐づくデータを取得する流れをイメージしてます。
4 完全外部結合
キーに紐づくデータを取得するが、両方のテーブルの行を必ず表示
完全外部結合の典型的な使用例は、下記のように、キーに紐づくデータを取得するが、
キーに紐づくデータが存在しなかったとしても、両方のテーブルの行を表示させるケースです。
完全外部結合は、似たような定義のテーブル同士でよく使われます。
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のイメージと似ています。
5 クロスジョイン
行の組み合わせを列挙
クロスジョインの典型的な使用例は、下記のように、行の組み合わせを列挙したいケースです。
Oracle9iからは、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行ごとに結合先の全行が紐づく様子をイメージしてます。
6 Partitioned Outer Join
パーティションを切ってできた、それぞれの集合と外部結合
Oracle10gから、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される様子をイメージしてます。
7 自己結合
同じテーブル同士で、行の組み合わせを列挙
自己結合の典型的な使用例は、下記のように、
同じテーブル同士で、行の組み合わせを列挙するケースです。
なお、同じテーブルとの結合であれば自己結合と呼ばれます。
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のイメージは下記です。
自己結合なので、テーブルのコピーをイメージし、
この場合は内部結合でもあるので、紐づくデータを取得する流れをイメージしてます。
参考リソース