しばらく使わないとつい忘れてしまうようなSQLをメモっています。
not exists
> select * from exp01
C1 C2
-- --------------------
1 みかん
2 りんご
3 いちご
4 <null>
> select * from exp02
C1 C2
-- --------------------
1 だいこん
2 にんじん
3 いちご
上記の場合以下のSQLでexp02に存在しないexp01のデータを抽出する事ができます。
exp02にはnullが入らないという前提ならこれで「まぁ、良いでしょう」
> select * from exp01 e1 where not exists (select 1 from exp02 e2 where e1.c2 = e2.c2) order by c1
C1 C2
-- --------------------
1 みかん
2 りんご
4 <null>
もし上記exp02にnullが入ってしまうと、、、
> select * from exp03
C1 C2
-- --------------------
1 だいこん
2 にんじん
3 いちご
4 <null> <-- exp02のデータにnullを加えました
> select * from exp01 e1 where not exists (select 1 from exp03 e3 where e1.c2 = e3.c2) order by c1
C1 C2
-- --------------------
1 みかん
2 りんご
4 <null> ←意図しないデータです。
※過去これが原因のバグをつい作ってしまったので戒めのためにこの例題を書きました。
nullを=で評価できないのでcoalesceを使い代替え文字で比較しています。
（nvlでもよいですがnvlはOracle独自関数なので、、）
> select * from exp01 e1 where not
> exists (select 1 from exp03 e3 where coalesce(e1.c2,'null') = coalesce(e3.c2,'null')) order by c1
C1 C2
-- --------------------
1 みかん
2 りんご
無事nullは表示されなくなりました。検証データは以下の通り作成しました
create table exp01 (c1 number,c2 varchar2(20)); create table exp02 (c1 number,c2 varchar2(20)); create table exp03 (c1 number,c2 varchar2(20)); insert into exp01(c1,c2) values(1,'みかん'); insert into exp01(c1,c2) values(2,'りんご'); insert into exp01(c1,c2) values(3,'いちご'); insert into exp01(c1,c2) values(4,null); insert into exp02(c1,c2) values(1,'だいこん'); insert into exp02(c1,c2) values(2,'にんじん'); insert into exp02(c1,c2) values(3,'いちご'); insert into exp03(c1,c2) values(1,'だいこん'); insert into exp03(c1,c2) values(2,'にんじん'); insert into exp03(c1,c2) values(3,'いちご'); insert into exp03(c1,c2) values(4,null);
相関サブクエリ
group by で指定する集計キー以外の項目を取得したい時便利です。 例えば以下のようなデータがあって、「各教科の最高得点者のレコードを表示したい」という事を実現します。単純に各教科毎の最高得点を出すのであれば、select subject,max(point) from tb30 group by subject と簡単ですが、「誰が最高得点者なのか」を表示したくなった時に活躍します。
SQL> select * from tb30 order by sid
2 ;
SID SUBJECT POINT NAME
______ __________ ________ _______
1 数学 75 西川
2 数学 91 東田
3 数学 91 北川
4 英語 65 南田
5 英語 85 上川
6 国語 95 中川
7 国語 87 下川
8 国語 80 赤川
9 国語 96 黒川
9行が選択されました。
SQL>select sid,subject,point,name from tb30 m where m.POINT = ( select max(t.POINT) from tb30 t where m.SUBJECT = t.SUBJECT group by t.SUBJECT ) order by m.SID ;
SQL> select sid,subject,point,name from tb30 m
2 where m.POINT = (
3 select max(t.POINT) from tb30 t
4 where m.SUBJECT = t.SUBJECT
5 group by t.SUBJECT
6 ) order by m.SID
7 ;
SID SUBJECT POINT NAME
______ __________ ________ _______
2 数学 91 東田
3 数学 91 北川
5 英語 85 上川
9 国語 96 黒川
SQL>相関サブクエリを使わないとこんな感じです
select m.SID,m.SUBJECT,m.point,m.NAME
from tb30 m
inner join
(
select subject, max(point) maxpoint from tb30
group by subject
) t
on m.SUBJECT = t.SUBJECT and m.POINT = t.MAXPOINT
order by m.SID
;
SQL> select m.SID,m.SUBJECT,m.point,m.NAME
2 from tb30 m
3 inner join
4 (
5 select subject, max(point) maxpoint from tb30
6 group by subject
7 ) t
8 on m.SUBJECT = t.SUBJECT and m.POINT = t.MAXPOINT
9 order by m.SID
10 ;
SID SUBJECT POINT NAME
______ __________ ________ _______
2 数学 91 東田
3 数学 91 北川
5 英語 85 上川
9 国語 96 黒川
SQL>ランク関数を使うとさらに理解しやすいかもです
select sid,subject,point,name from
(
select sid,subject,point,name,rank() over (partition by subject order by point desc) jyuni
from tb30
) where jyuni =1
order by 1
;
SQL> select sid,subject,point,name from
2 (
3 select sid,subject,point,name,rank() over (partition by subject order by point desc) jyuni
4 from tb30
5 ) where jyuni =1
6 order by 1
7 ;
SID SUBJECT POINT NAME
______ __________ ________ _______
2 数学 91 東田
3 数学 91 北川
5 英語 85 上川
9 国語 96 黒川
SQL>検証データは以下の通り作成しました
create table tb30(sid number,subject varchar2(20),point number,name varchar2(10) ); insert all into tb30(sid,subject,point,name) values (1,'数学',75,'西川') into tb30(sid,subject,point,name) values (2,'数学',91,'東田') into tb30(sid,subject,point,name) values (3,'数学',91,'北川') into tb30(sid,subject,point,name) values (4,'英語',65,'南田') into tb30(sid,subject,point,name) values (5,'英語',85,'上川') into tb30(sid,subject,point,name) values (6,'国語',95,'中川') into tb30(sid,subject,point,name) values (7,'国語',87,'下川') into tb30(sid,subject,point,name) values (8,'国語',80,'赤川') into tb30(sid,subject,point,name) values (9,'国語',96,'黒川') select 'X' from dual; commit ;
複数テーブルの外部結合
SELECT T1.C1,T1.C2,T3.C2 T3_C2
,DECODE(T2.C2,NULL,'XXX',T2.C2) T3_C2
FROM T1
INNER JOIN T3 ON (T1.C1 = T3.C1)
LEFT OUTER JOIN T2 ON (T1.C1 = T2.C1)
WHERE T1.C1 != 5 AND T3.C1 !=7;
C1 C2 T3_C2 T2_C2
-- --- ----- -----
2 AA cc BB
3 AA cc xxx
検証データ
create table t1 (c1 number,c2 varchar2(10));
create table t2 (c1 number,c2 varchar2(10));
create table t3 (c1 number,c2 varchar2(10));
insert all
into t3(c1,c2) values (2,'cc')
into t3(c1,c2) values (3,'cc')
into t3(c1,c2) values (4,'cc')
select * from dual;
insert all
into t1(c1,c2) values (1,'AA')
into t1(c1,c2) values (2,'AA')
into t1(c1,c2) values (3,'AA')
select * from dual;
insert all
into t2(c1,c2) values (2,'BB')
into t2(c1,c2) values (5,'BB')
select * from dual;結合UPDATE
簡単に更新するなら
UPDATE ex1 A
SET (c2,c3)=(SELECT c2,c3 FROM ex2 b WHERE b.c1=a.c1);高速に更新するなら
UPDATE
(
SELECT a.c2 ac2,b.c2 bc2,a.c3 ac3,b.c3 bc3 FROM
ex1 a,ex2 b
where a.c1 = b.c1
)
SET
ac2 = bc2
,ac3 = bc3;
この時の注意点として結合するテーブル側のカラム（ここではex2.c1）には
unique index が作成されていないといけない。 とても便利な分析関数
まずは検証データ作成から。
create table EX005
(
c1 number,
c2 varchar2(10),
c3 varchar2(10),
c4 number
);
insert all
into ex005(c1,c2,c3,c4) values(1,'2009','大西',100)
into ex005(c1,c2,c3,c4) values(2,'2009','中西',50)
into ex005(c1,c2,c3,c4) values(3,'2009','小西',30)
into ex005(c1,c2,c3,c4) values(4,'2010','大西',60)
into ex005(c1,c2,c3,c4) values(5,'2010','中西',60)
into ex005(c1,c2,c3,c4) values(6,'2010','小西',70)
into ex005(c1,c2,c3,c4) values(7,'2011','大西',100)
into ex005(c1,c2,c3,c4) values(8,'2011','中西',100)
into ex005(c1,c2,c3,c4) values(9,'2011','小西',90)
select 'X' from dual;row_number()を使用したケース
select c1,c2,c3,c4,row_number() over (partition by c2 order by c4 desc) jyuni
from ex005 order by c2,jyuni;
C1 C2 C3 C4 JYUNI
---------- ---------- ---------- ---------- ----------
1 2009 大西 100 1
2 2009 中西 50 2
3 2009 小西 30 3
6 2010 小西 70 1
4 2010 大西 60 2
5 2010 中西 60 3
7 2011 大西 100 1
8 2011 中西 100 2
9 2011 小西 90 3
順位はかならず１から順番に付与されます。
rank()を使用したケース
select c1,c2,c3,c4,rank() over (partition by c2 order by c4 desc) jyuni
from ex005 order by c2,jyuni;
C1 C2 C3 C4 JYUNI
---------- ---------- ---------- ---------- ----------
1 2009 大西 100 1
2 2009 中西 50 2
3 2009 小西 30 3
6 2010 小西 70 1
4 2010 大西 60 2
5 2010 中西 60 2
7 2011 大西 100 1
8 2011 中西 100 1
9 2011 小西 90 3
同じ順位の後は飛び番号となります。
dense_rank()を使用したケース
select c1,c2,c3,c4,dense_rank() over (partition by c2 order by c4 desc) jyuni
from ex005 order by c2,jyuni;
C1 C2 C3 C4 JYUNI
---------- ---------- ---------- ---------- ----------
1 2009 大西 100 1
2 2009 中西 50 2
3 2009 小西 30 3
6 2010 小西 70 1
4 2010 大西 60 2
5 2010 中西 60 2
7 2011 大西 100 1
8 2011 中西 100 1
9 2011 小西 90 2
順位が欠番になる事はありません。
おまけ（Yahoo辞書より）
dense[ dns ]
形]（dens・er, dens・est）
1 濃密な，濃い，深い；密集した；（葉の）よく繁った；目の詰んだ
