Articles
図でイメージするOracle DatabaseのSQL全集第2回 集合演算など
|
| 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
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としつつ、集合同士の和集合を取得する流れをイメージしています。
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
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集合演算のイメージと同じです。
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関数で集約する使い方もあります。
参考リソース
- マニュアル --- UNION [ALL]、INTERSECTおよびMINUS演算子
- OracleSQLパズル 3-38 Outer Union (外和集合演算)
- OracleSQLパズル 10-226 OverLaps述語
"図でイメージするOracle DatabaseのSQL全集" インデックスに戻る
|
Copyright © 2010, Oracle Corporation Japan. All rights reserved.
無断転載を禁ず |
この文書はあくまでも参考資料であり、掲載されている情報は予告なしに変更されることがあります。日本オラクル社は本書の内容に関していかなる保証もいたしません。また、本書の内容に関連したいかなる損害についても責任を負いかねます。 Oracleは米国Oracle Corporationの登録商標です。文中に参照されている各製品名及びサービス名は米国Oracle Corporationの商標または登録商標です。その他の製品名及びサービス名はそれぞれの所有者の商標または登録商標の可能性があります。 |
山岸 賢治（やまぎし けんじ）
Oracle ACEの1人。
OracleSQLパズルの運営者。
 |
図でイメージする
Oracle DatabaseのSQL全集 |
 |
Printer View