読者です 読者をやめる 読者になる 読者になる

カタカタブログ

SIerで働くITエンジニアがカタカタした記録を残す技術ブログ。Java, Oracle Database, Linuxが中心です。たまにRuby on Railsなども。

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

Oracle DB 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

上の3つの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

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