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行だけ返しているのでこれらはスカラサブクエリである。

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

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

なお、検証は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値が多数含まれている。

このとき、以下の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は全て以下の同じ検索結果となる。

このスカラサブクエリの場合、FIRST_NAMEとLAST_NAMEでそれぞれ別クエリにしなければならないため、効率が悪くなるが、
外部結合対象の項目が1つだけならば、スカラサブクエリ化することでパフォーマンスが向上することもある。