図でイメージするOracleのSQL全集の元原稿   第3回 分析関数   第1回 内部結合や外部結合など

第2回 集合演算など


連載のテーマ,動作確認環境

前回と同じとなります。


今回のテーマ

今回は、下記のOracleのSQL文の評価順序においての、
10番目の集合演算、2番目と5番目であるwhere句、9番目のselect句に関連した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句


目次

 1. union集合演算
 2. Outer unionもどき
 3. intersect集合演算 (exists述語、in述語、内部結合)
 4. minus集合演算 (not exists述語、not in述語)
 5. select句での相関サブクエリ


1 union集合演算

集合同士の和集合を取得

union集合演算の典型的な使用例は、下記のように、集合同士の和集合を取得するケースです。

create table unionT1(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 unionT2(ID primary key,Val) as
select 111,100 from dual union all
select 222,500 from dual union all
select 666,600 from dual;

unionT1テーブルに存在する行、または、unionT2テーブルに存在する行を出力します。

-- union集合演算を使ったSQL
select ID,Val
  from unionT1
union
select ID,Val
  from unionT2
order by ID,Val;

出力結果
ID   Val
---  ---
111  100
222  200
222  500
555  300
666  600

union集合演算のSQLのイメージは下記です。完全外部結合のイメージと似ています。
和集合演算のベン図と、集合同士の和集合を取得する流れをイメージしてます。
union集合演算のイメージ

なお、下記のSQLのように、
union集合演算でallオプションを指定し、重複行を排除しないようにすることも可能です。

-- union all集合演算を使ったSQL
select ID,Val
  from unionT1
union all
select ID,Val
  from unionT2
order by ID,Val;

出力結果
ID   Val
---  ---
111  100
111  100
222  200
222  500
555  300
666  600


2 Outer unionもどき

不足列をnullとして、集合同士の和集合を取得

Outer unionもどきを使うと、
下記のように、union all集合演算で、片方の集合にしか存在しない列をnullとして取得できます。
sum関数などで集約をする際の準備でOuter unionもどきを使うことが多いです。

create table OuterUnionT1(ID,Val1) as
select 111, 10 from dual union all
select 111, 30 from dual union all
select 222,120 from dual;

create table OuterUnionT2(ID,Val2) as
select 111,200 from dual union all
select 333,300 from dual;

OuterUnionT1テーブルに存在する行、または、OuterUnionT2テーブルに存在する行を
片方の集合にしか存在しない列をnullとして出力します。

-- Outer unionもどきを使ったSQL
select ID,Val1,to_number(null) as Val2
  from OuterUnionT1
union all
select ID,to_number(null),Val2
  from OuterUnionT2
order by ID,Val1,Val2;

出力結果
ID   Val1  Val2
---  ----  ----
111    10  null
111    30  null
111  null   200
222   120  null
333  null   300

Outer unionもどきのSQLのイメージは下記です。
和集合演算のベン図と、
片方の集合にしか存在しない列をnullとしつつ、集合同士の和集合を取得する流れをイメージしてます。
Outer unionもどきのイメージ


3 intersect集合演算 (exists述語、in述語、内部結合)

集合同士の共通集合を取得

intersect集合演算の典型的な使用例は、下記のように、集合同士の共通集合を取得するケースです。

create table masterT(ID primary key,Val) as
select 111,10 from dual union all
select 333,80 from dual union all
select 555,60 from dual union all
select 777,50 from dual union all
select 999,30 from dual;

create table tranT(ID primary key,kosuu) as
select 111,13 from dual union all
select 222,46 from dual union all
select 555,78 from dual;

masterTテーブルに存在するIDで、tranTテーブルにも存在するIDを出力します。

-- intersect集合演算を使ったSQL
select ID
  from masterT
intersect
select ID
  from tranT
order by ID;

出力結果
ID
---
111
555

intersect集合演算のSQLのイメージは下記です。内部結合のイメージと同じイメージです。
共通集合演算のベン図と、集合同士の共通集合を取得する流れをイメージしてます。
intersect集合演算のイメージ

なお、intersect集合演算は、select文の結果として出力したい列リストと、
存在を確認したい列リストが一致しているケースでしか使えません。

なので、tranTテーブルに同じIDが存在する、masterTテーブルのID列とVal列を出力したいような場合は、
intersect集合演算ではなく、下記のSQLのようにexists述語やin述語を使う必要があります。

-- exists述語を使ったSQL
select ID,Val
  from masterT a
 where exists(select 1 from tranT b
               where b.ID = a.ID)
order by ID;

-- in述語を使ったSQL
select ID,Val
  from masterT a
 where ID in(select ID from tranT)
order by ID;

出力結果
ID   Val
---  ---
111   10
555   60

exists述語やin述語のイメージは、上記のintersect集合演算のイメージと同じです。

なお、masterTテーブルのIDに紐づくtranTテーブルのkosuu列も出力したいような場合は、
exists述語やin述語ではなく、下記のSQLのように内部結合を使う必要があります。
内部結合のイメージも、上記のintersect集合演算のイメージと同じです。

-- 内部結合を使ったSQL
select a.ID,a.Val,b.kosuu
  from masterT a Join tranT b
    on a.ID = b.ID
order by a.ID;

出力結果
ID   Val  kosuu
---  ---  -----
111   10     13
555   60     78


4 minus集合演算 (not exists述語、not in述語)

集合同士の差集合を取得

minus集合演算の典型的な使用例は、下記のように、集合同士の差集合を取得するケースです。

masterTテーブルに存在するIDで、tranTテーブルに存在しないIDを出力します。

-- minus集合演算を使ったSQL
select ID
  from masterT
minus
select ID
  from tranT
order by ID;

出力結果
ID
---
333
777
999

minus集合演算のSQLのイメージは下記です。
差集合演算のベン図と、集合同士の差集合を取得する流れをイメージしてます。
minus集合演算のイメージ

なお、minus集合演算は、select文の結果として出力したい列リストと、
存在しないことを確認したい列リストが一致しているケースでしか使えません。

なので、tranTテーブルに同じIDが存在しない、masterTテーブルのID列とVal列を出力したいような場合は、
minus集合演算ではなく、下記のSQLのようにnot exists述語やnot in述語を使う必要があります。

-- not exists述語を使ったSQL
select ID,Val
  from masterT a
 where not exists(select 1 from tranT b
                   where b.ID = a.ID)
order by ID;

-- not in述語を使ったSQL
select ID,Val
  from masterT a
 where ID not in(select ID from tranT)
order by ID;

出力結果
ID   Val
---  ---
333   80
777   50
999   30

not exists述語やnot in述語のイメージは、上記のminus集合演算のイメージと同じです。


5 select句での相関サブクエリ

列値を使ったサブクエリの結果を取得

select句での相関サブクエリの典型的な使用例は、下記のように、
exists述語などで、列値を使ったサブクエリの結果を取得するケースです。

create table NumTable(ID primary key,staNum,EndNum) as
select  1,10,20 from dual union all
select  3,15,25 from dual union all
select  5,30,40 from dual union all
select  6,50,58 from dual union all
select 11,53,54 from dual union all
select 13,52,59 from dual union all
select 16,70,75 from dual union all
select 18,76,80 from dual;

他のIDと、staNumからEndNumまでの範囲がOverLapsしているかをチェックします。

-- select句での相関サブクエリを使ったSQL
select ID,staNum,EndNum,
case when exists(select 1 from NumTable b
                  where b.ID != a.ID
                    and b.staNum <= a.EndNum
                    and b.EndNum >= a.staNum)
     then 1 else 0 end as IsOverLaps
from NumTable a
order by ID;

出力結果
ID  staNum  EndNum  IsOverLaps
--  ------  ------  ----------
 1      10      20           1
 3      15      25           1
 5      30      40           0
 6      50      58           1
11      53      54           1
13      52      59           1
16      70      75           0
18      76      80           0

上記のSQLのイメージは下記です。exists述語で存在チェックを行う流れをイメージしてます。
上記のSQLのイメージ

なお、select句での相関サブクエリには、他テーブルの値をsum関数で集約する使い方などもあります。


参考リソース

マニュアル --- UNION [ALL]、INTERSECTおよびMINUS演算子

OracleSQLパズル 3-38 Outer Union (外和集合演算)
OracleSQLパズル 10-226 OverLaps述語