図でイメージする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集合演算
集合同士の和集合を取得
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のイメージは下記です。完全外部結合のイメージと似ています。
和集合演算のベン図と、集合同士の和集合を取得する流れをイメージしてます。
なお、下記の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としつつ、集合同士の和集合を取得する流れをイメージしてます。
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集合演算は、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集合演算は、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述語で存在チェックを行う流れをイメージしてます。
なお、select句での相関サブクエリには、他テーブルの値をsum関数で集約する使い方などもあります。
参考リソース