Lightbox Info Board : SQLの窓

副問い合わせが使用できる句?

ある書籍では、副問い合わせが使用できる句としてfrom・where・having としていますが、実際は select でも使用できます。これは、Oracle に限らず常識的な事だと思っていたのですが何故か select が勘定に入っていません。

そもそも、問題として考えた時にどの「句」で使用できるかというのはちょっと強引なような気がしますが、問題を作った人にそれなりの意図があったのかとは思います。しかし、謎はそれだけではありません。

スカラー副問合せ式

Oracle のマニュアルでの表現ですが、要するに1つの列を指定した１行を戻す副問い合わせは式として使用できるという事なのですが、もっともな事で、これは副問合せを使用する時の基本的なガイドラインではあります。

行を戻さない時は、NULL であり、2行以上を戻す時はエラーです。全く問題ありません。しかし、このような但し書きがあります。


次の場所では、スカラー副問合せは無効です

列のデフォルト値
クラスタのハッシュ式
DML 文RETURNING 句
ファンクション索引の基礎
CHECK 制約
CASE 式のWHEN 条件
GROUP BY 句およびHAVING 句
START WITH 句およびCONNECT BY 句
CREATE PROFILE などの問合せに関連しない文

何故 HAVING 句があるのでしょう?

HAVING 句での副問合せ


select
	deptno,
	count(deptno)
from emp
group by deptno
having
	count(deptno) > (select avg(count(deptno)) from emp group by deptno);

これは、「そんなバカな」と思って作ったテストクエリーですが、各部署に所属する人数の平均より多い人数が所属する部署を表示するものです。結果は以下のようになります

	DEPTNO	COUNT(DEPTNO)
1	20	5
2	30	6

当然ですが、HAVING 句を指定しなければ以下のようになります。平均は4.66666666666667です

	DEPTNO	COUNT(DEPTNO)
1	10	3
2	20	5
3	30	6

SELECT 句での副問合せ


select ename,(select ename from emp where empno = e.mgr) as 管理者 from emp e

	ENAME	管理者
1	SMITH	FORD
2	ALLEN	BLAKE
3	WARD	BLAKE
4	JONES	KING
5	MARTIN	BLAKE
6	BLAKE	KING
7	CLARK	KING
8	SCOTT	JONES
9	KING
10	TURNER	BLAKE
11	ADAMS	SCOTT
12	JAMES	BLAKE
13	FORD	JONES
14	MILLER	CLARK

これは、自己結合で良く引き合いに出される社員と社員の管理者の関係を示すものです。結合のほうが検索速度は優れているような気はしますが、帳票出力というような実用の際はこのような使い方をする場面が少なくないと思います。