SQL 外部結合と等価なスカラサブクエリについて

SQLの外部結合とスカラサブクエリの等価性について調べてみたので結果をまとめてみる。

スカラサブクエリとは

スカラサブクエリとはスカラ値(つまり単一値)を返すクエリのことである。
SQLにおけるスカラ値を返すとは、単一列の値を1行だけ返すようなクエリのことである。
例えば、主キーでしぼったり、rownum = 1やlimit 1などとして絶対に1行しかヒットしない条件句を持ち、
from句にも1つの項目しか指定しないようなクエリはスカラサブクエリである。

例1 :主キーでしぼったスカラサブクエリ

select d.DEPARTMENT_NAME from hr.DEPARTMENTS d
where d.DEPARTMENT_ID = 10

例2 :rownum=1を指定したスカラサブクエリ

select d.DEPARTMENT_NAME from hr.DEPARTMENTS d
where rownum = 1

上の検索結果はどちらも以下。1列だけの値を1行だけ返しているのでこれらはスカラサブクエリである。

DEPARTMENT_NAME
Administration

スカラサブクエリと外部結合の等価性について

以下、スカラサブクエリと外部結合の等価性について見ていく。

なお、検証はOracle DBで行っているが、基本的な考え方は他のDBMSでも同じ。
検証用スキーマとしてOracle標準のHRサンプルスキーマで実験した。

まず、検証対象データセットとして、HR.DEPARTMENTSを見る。

select * from hr.DEPARTMENTS d
order by d.DEPARTMENT_ID

検索結果は以下。
DEPARTMENTS.MANAGER_IDの外部キーがEMPLOYEES.EMPLOYEE_IDになっている。
また、DEPARTMENTS.MANAGER_ID列にはNULL値が多数含まれている。

DEPARTMENT_ID	DEPARTMENT_NAME	MANAGER_ID	LOCATION_ID
10	Administration	200	1700
20	Marketing	201	1800
30	Purchasing	114	1700
40	Human Resources	203	2400
50	Shipping	121	1500
60	IT	103	1400
70	Public Relations	204	2700
80	Sales	145	2500
90	Executive	100	1700
100	Finance	108	1700
110	Accounting	205	1700
120	Treasury		1700
130	Corporate Tax		1700
140	Control And Credit		1700
150	Shareholder Services		1700
160	Benefits		1700
170	Manufacturing		1700
180	Construction		1700
190	Contracting		1700
200	Operations		1700
210	IT Support		1700
220	NOC		1700
230	IT Helpdesk		1700
240	Government Sales		1700
250	Retail Sales		1700
260	Recruiting		1700
270	Payroll		1700

このとき、以下の3つのSQLは等価になる。

スカラサブクエリ

select
  d.*
, (select e.FIRST_NAME from hr.EMPLOYEES e where d.MANAGER_ID = e.EMPLOYEE_ID) AS MANANGER_FIRST_NAME
, (select e.LAST_NAME from hr.EMPLOYEES e where d.MANAGER_ID = e.EMPLOYEE_ID) AS MANANGER_LAST_NAME
from
  hr.DEPARTMENTS d
order by d.DEPARTMENT_ID

外部結合(Oracle記法)

select
  d.*
, e.FIRST_NAME AS MANANGER_FIRST_NAME
, e.LAST_NAME AS MANANGER_LAST_NAME
from
  hr.DEPARTMENTS d
, hr.EMPLOYEES e
where d.MANAGER_ID = e.EMPLOYEE_ID(+)
order by d.DEPARTMENT_ID

外部結合(ANSI記法)

select
  d.*
, e.FIRST_NAME AS MANANGER_FIRST_NAME
, e.LAST_NAME AS MANANGER_LAST_NAME
from
  hr.DEPARTMENTS d
left outer join hr.EMPLOYEES e
on d.MANAGER_ID = e.EMPLOYEE_ID
order by d.DEPARTMENT_ID

上の３つのSQLは全て以下の同じ検索結果となる。

DEPARTMENT_ID	DEPARTMENT_NAME	MANAGER_ID	LOCATION_ID	MANANGER_FIRST_NAME	MANANGER_LAST_NAME
10	Administration	200	1700	Jennifer	Whalen
20	Marketing	201	1800	Michael	Hartstein
30	Purchasing	114	1700	Den	Raphaely
40	Human Resources	203	2400	Susan	Mavris
50	Shipping	121	1500	Adam	Fripp
60	IT	103	1400	Alexander	Hunold
70	Public Relations	204	2700	Hermann	Baer
80	Sales	145	2500	John	Russell
90	Executive	100	1700	Steven	King
100	Finance	108	1700	Nancy	Greenberg
110	Accounting	205	1700	Shelley	Higgins
120	Treasury		1700
130	Corporate Tax		1700
140	Control And Credit		1700
150	Shareholder Services		1700
160	Benefits		1700
170	Manufacturing		1700
180	Construction		1700
190	Contracting		1700
200	Operations		1700
210	IT Support		1700
220	NOC		1700
230	IT Helpdesk		1700
240	Government Sales		1700
250	Retail Sales		1700
260	Recruiting		1700
270	Payroll		1700