SQL NATURAL JOIN 自然結合 (というか結合のあれこれ)

SQLの基本はテーブル。テーブル同士を結合するようなSELECT命令を作成して、帳票出力なり、画面表示なりを行っていくことが基本である。つまり結合は基本なわけであるが、その当たりの詳細は、「[データベースの気持ちがわかる]SQLはじめの一歩」を是非読んでみて欲しい。

さて、結合の方法には、歴史がある。RDBMSは、古くからある。最初のレガシーな結合は、というと以下のような感じで行っていた。何せ、SQLには歴史があるからねぇ。

SELECT * FROM foo, bar WHERE foo.a = bar.a

FROM句には、テーブルを列挙するが、ここにはアクセスするテーブル名だけを書く。結合条件は書かない。
WHEREに行を選択する条件式を書くのだが、レガシーなSQLでは結合条件もここに書くのである。
結合の基本は「交差結合」。なので基本は、交差結合で得られる「直積」の中から「条件を付けて選び出す」というのが、結合の基本であるわけである。
一方、NATURAL JOINは次のように書くことができる

SELECT * FROM foo NATURAL JOIN bar

結合条件がテーブルの列から自動的に決定するので条件式を書かなくてよい。ここが便利なところである。
SQLのSELECT命令では結合を行うことが多いのだが、いろいろとやり方があり、歴史的に変化もしてきている。ちょっと結合のやり方を整理してみよう。

外部結合

普通の結合は、普通の画面や帳票を作る上では、普通に便利であった。直積から、条件に一致する行だけを抽出する、といった単純な処理方法であるため、扱いやすかった。
しかし、である。
「例外」というのは、いつの世の世界でも存在するもので「データがまだ揃っていない状況でも画面表示したい」とか、バグで「データがない状態でもなんとか表示させたい」というあまり「美しくない理由」で"外部結合"という方法が編み出された(と思う。あくまで推測です)。
考え方自体はどれも同じであったが、実装方法が異なっていた。
例えば、Oracleでは、外部結合を以下のように行っていた。

SELECT * FROM foo, bar WHERE foo.a = bar.a(+)

「(+)」っていうなんか得体の知れない、独特の「演算子みたいなもの」を作ってしまったのである。
これに対して、MS SQL Serverでは、以下のように外部結合することができた。

SELECT * FROM foo, bar WHERE foo.a *= bar.a

演算子を追加することで、外部結合ができるようにしたのだが... Oracleとは違う。
そう、いわゆる方言なわけです。
この辺りのことは「[データベースの気持ちがわかる]SQLはじめの一歩」にはあまり書いてない。SQLポケットリファレンスには書いてあるか。

どちらかというと、Oracleより、SQL Serverの方が素直な感じ、というか、自分の考えに近いので、受け入れ易いと思う。開発者としては、演算子が増えるのには、意外と抵抗がない。しかし、Oracleの外部結合のように、式のわけがわからない位置に、わけわからない、記号が来ると「パニック」になるわけです。(+)っていう演算子なんて「Oracle以外で見たことないし！」。
どちらが良いかは置いておいて、同じことをするのに、データベースごとにやり方が異なると、開発者は困るわけです。どっちにも対応できるように作っておけ、と言われてしまうと、#ifdefの山になってしまうわけで... これはソースを見ると「読み難い」んです。とっても。
でもって、標準化団体が動き出したわけです。
結合は、「JOIN」を使ってやりましょう。ということになったのである。JOINはFROM句に書く。結合には、交差結合、内部結合、外部結合などいろいろな種類がある。JOINでは、その前に種類を指定することができる。
　CROSS JOIN なら、交差結合。
　INNER JOIN なら、内部結合。
　LEFT OUTER JOIN なら、左側のテーブルを残す外部結合。
　RIGHT OUTER JOIN なら、右側。
　FULL OUTER JOIN なら、両方残す。
と決めたわけです。
交差結合を除いて、結合条件を指定しなければならない。WHEREに書いてきたやつですね。JOINの左右にはテーブル名を書くのだが、その後に「ONを付けて、結合条件式を書く」と決定したのです。

SELECT * FROM foo, bar WHERE foo.a = bar.a

という内部結合なら、以下のようにJOINで書くことができる。

SELECT * FROM foo INNER JOIN bar ON foo.a = bar.a

外部結合でもやってみるか。

SELECT * FROM foo, bar WHERE foo.a = bar.a(+)
SELECT * FROM foo, bar WHERE foo.a *= bar.a

どちらもLEFT JOINを使って、

SELECT * FROM foo LEFT JOIN bar ON foo.a = bar.a

とすればOK。
OUTERは省略できる。
FULL OUTER JOINは、(+)や*=では、できない。
Oracleの(+)方式でやろうと思ったら、以下のようになる。

SELECT * FROM foo, bar WHERE foo.a(+) = bar.a(+)

SQL Server方式なら以下

SELECT * FROM foo, bar WHERE foo.a *=* bar.a

(+)の連続や、*=*演算子は使えないので、どちらもエラーになる。
FULL OUTER JOINはサポートしているデータベースなら実行できる。

SELECT * FROM foo FULL OUTER JOIN bar ON foo.a = bar.a

どうでもいいが、syntax highlighterでOUTERとかJOINがハイライトされないが...
JOINくらいはあってもいいのではないかと...
後で見てみよう。
ついでに、CROSS JOINもやってみようか

SELECT * FROM foo CROSS JOIN bar

CROSS JOINでは結合条件がない。
USING
時代が進むと、USINGを使った結合条件を書くことも可能になってきた。
普通にテーブルを作成したら、プライマリキーとなっている列は、列名に「気を配って」作成される。商品マスタの商品コード列は、syohin_cdという名前を付けておいて、別のテーブルで商品マスタを参照する場合は、syohin_cdという同じ名前の列を作成することでしょう。
なので、結合条件は、以下のような感じになることが多い。

syohin INNER JOIN uriage ON syohin.syohin_cd = uriage.syohin_cd

結構、式が長いよね。
結合条件が同じ列名であるのなら、USINGを使うことができる。

syohin INNER JOIN uriage USING(syohin_cd)

おお、これなら書く量が少なくて済む。いいね。
USINGだと、括弧の中には、列名しか書けない。結合の両方のテーブルで「同じ列名であることが前提」となる。
同じ列名であっても、型が微妙に違っていたりすると問題が発生するかも知れない。
普通のテーブル設計では、データの紐付けに使用する列は、列名、型とも一致させておくことがセオリーであろう。USINGはそのようなスキーマになっていれば、問題なくUSINGを使うことができる。
USINGでは、条件式で使われる演算子は、=に限定される。BETWEENやら>を使うときは、ONにしないといけない。まぁ、普通の結合では、=しか使わないので、この点については問題ないかと。
外部結合でもUSINGが使えることもある(データベース依存?)。
SQL Server、DB2、AccessではUSINGは使えない。

NATURAL JOIN

長々と説明してきたが、今回紹介したかったのはNATURAL JOINである。NATURAL JOINは「一番新しい結合の方法」かもしれない。
すみません。NATURAL JOINがなかなか出てこないので、ブログのタイトルを少し変えました。

NATUAL JOINを使うと条件式を記述しなくてもよい。結合するテーブル同士で同じ名前の列をすべて等しい条件に自動的になる。
テーブルfooとbarが以下のようにスキーマ定義されていたとする。

CREATE TABLE foo (
a INTEGER,
b INTEGER
)
CREATE TABLE bar (
a INTEGER,
c VARCHAR(20)
)

NATURAL JOINすると結合条件は、暗黙的に「foo.a = bar.a」となる。

SELECT * FROM foo NATURAL JOIN bar

NATURAL JOINは、Oracle、MySQL、PostgreSQL、SQLiteで使用できる。
本日は以上。