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

第2回 集合演算など
Oracle SQLの各機能をイメージ図を交えて解説

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

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

目次

今回のテーマ

今回は、問い合わせ結果を組みあわせることができる「集合演算」と、異なる行同士を比較できる「相関サブクエリ」をとりあげます。前回と同様にOracleのSelect文の評価順序で説明しますと、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句

動作確認環境

Oracle Database 11g Release 11.2.0.1.0 (windows 32ビット版)

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集合演算に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

▲ ページTOPに戻る

Outer unionもどき

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

現在、Oracle SQLではOuter Union (外和集合演算)を利用することができませんが、下記のように“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としつつ、集合同士の和集合を取得する流れをイメージしています。

▲ ページTOPに戻る

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集合演算は、select文の結果として出力したい列リストと、存在を確認したい列リストが一致しているケースでしか使えません。

そのため、tranTテーブルに同じIDが存在する、masterTテーブルのID列とVal列を出力したいといった場合、intersect集合演算ではなく、下記のように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述語ではなく、下記のように内部結合を使う必要があります。
内部結合のイメージも、上記の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

▲ ページTOPに戻る

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

そのため、tranTテーブルに同じIDが存在しない、masterTテーブルのID列とVal列を出力したいといった場合、minus集合演算ではなく、下記のように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集合演算のイメージと同じです。

▲ ページTOPに戻る

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述語で存在チェックを行う流れをイメージしています。

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

▲ ページ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