SELECT文

OracleのSQLのSELECT文のメモ。

group by

group byのソート順

Oracle9iまでは、集計（group by）した場合はソートされる為、order byが不要だった。[2008-10-29]
が、これはOracleの独自仕様（内部仕様）だったらしく、Oracle10g以降はソートされるとは限らなくなったらしい。

つまりgroup byであっても、並び順を保証したいならorder byが必須となる。
group byだけでなく、distinctも同様。

• OTN掲示板のSQLにてorder by句がない場合
• OTN掲示板のGROUP　BYによるソート処理存在するか？

left join

2つのテーブルを結合（join）する際に、片方にデータが無い場合でも主たるテーブルのデータを全部取ってくるのがleft join（あるいはright join）。[2010-09-01]

WHERE句で、従属するテーブル側に「(+)」を付ける。これはOracleの独自構文。
（標準SQLでは、WHERE句でなく、FROM句に「join〜on」を書く。Oracle9i以降でもこの構文を使えるが、意外とバグ持ち（複雑な結合は危険）なので、後の方のバージョンでないと使わない方がいいかも）

select *
from
	主たるテーブル a,
	従属テーブル   b
where
	b.キー1(+) = a.キー1 and
	b.キー2(+) = a.キー2
;

select *
from
	主たるテーブル a
left join 従属テーブル b
on
	b.キー1 = a.キー1 and
	b.キー2 = a.キー2
;

select *
from
	主たるテーブル a,
	従属テーブル   b
where
	b.キー1(+) = a.キー1 and
	b.キー2(+) = a.キー2 and
	b.項目(+)  = '定数値'
;

select *
from
	主たるテーブル a,
	(select *
	  from 従属テーブル
	  where 項目 = '定数値'
	) b
where
	b.キー1(+) = a.キー1 and
	b.キー2(+) = a.キー2 and
;

select *
from
	主たるテーブル a
left join 従属テーブル b
on
	b.キー1 = a.キー1 and
	b.キー2 = a.キー2 and
	b.項目  = '定数値'
;

select *
from
	主たるテーブル a
left join (select *
	  from 従属テーブル
	  where 項目 = '定数値'
	) b
on
	b.キー1 = a.キー1 and
	b.キー2 = a.キー2 and
;

select *
from
	主たるテーブル a,
	従属テーブル   b
where
	b.キー1(+) = a.キー1 and
	b.キー2(+) = a.キー2 and

	b.項目 = '定数値'
;

select *
from
	主たるテーブル a
left join 従属テーブル b
on
	b.キー1 = a.キー1 and
	b.キー2 = a.キー2
where
	b.項目 = '定数値'
;

select *
from
	主たるテーブル a,
	従属テーブル   b
where
	b.キー1(+) = a.キー1 and
	b.キー2(+) = a.キー2 and

	b.キー1 is null
;

select *
from
	主たるテーブル a
left join 従属テーブル b
on
	b.キー1 = a.キー1 and
	b.キー2 = a.キー2
where
	b.キー1 is null
;

テーブルコレクション式

Oracleでは、create tableで作成するテーブルとは別に、データを配列やリストのようにまとめて扱う“テーブル”が存在する。[2010-07-24]

例えばnumber(4)のテーブル（というデータ型）は、以下の様にして定義できる。

SQL> create type myNumber4List is table of number(4);
  2  /

型が作成されました。

このデータ型を使って、値一覧を返す関数が作れる。

create or replace function empnoLessSal(arg1 in number) return myNumber4List
is
	ret myNumber4List;
begin
	select EMPNO bulk collect into ret from EMP where SAL < arg1;
	return ret;
end;
/

テーブル型を返す関数は、普通の関数の様に呼び出せる。

SQL> select empnoLessSal(11100) from dual;

EMPNOLESSSAL(11100)
--------------------------------------------------------------------------------
MYNUMBER4LIST(7369, 7876, 7900)

また、FROM句で使えるTABLEファンクションを使って、各データを行ごとに振り分けて通常のテーブルの様に使うことが出来る。（コレクションのネスト解除）

SQL> select * from table(empnoLessSal(11100));

COLUMN_VALUE
------------
        7369
        7876
        7900

• Oracle Database SQL言語リファレンスのtable_collection_expression