副問合せの謎

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



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

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



  スカラー副問合せ式



Oracle のマニュアルでの表現ですが、要するに1つの列を指定した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

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











  infoboard   管理者用   
このエントリーをはてなブックマークに追加





フリーフォントWEBサービス
SQLの窓WEBサービス

SQLの窓フリーソフト

素材

一般WEBツールリンク

SQLの窓

フリーソフト

JSライブラリ