So-net無料ブログ作成
検索選択

SQL NATURAL JOIN [SQLポケリ]

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

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

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


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

この当たりのことも「[データベースの気持ちがわかる]SQLはじめの一歩」に書いてあるので、是非読んでみて欲しい。

外部結合

普通の結合は、普通の画面や帳票を作る上では、普通に便利であった。直積から、条件に一致する行だけを抽出する、といった単純な処理方法であるため、扱いやすかった。

しかし、である。

「例外」というのは、いつの世の世界でも存在するもので「データがまだ揃っていない状況でも画面表示したい」とか、バグで「データがない状態でもなんとか表示させたい」というあまり「美しくない理由」で"外部結合"という方法が編み出された(と思う。あくまで推測です)。

考え方自体はどれも同じであったが、実装方法が異なっていた。
例えば、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


ついでに、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は「一番新しい結合の方法」かもしれない。
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 NATUAL JOIN bar


NATURAL JOINは、Oracle、MySQL、PostgreSQL、SQLiteで使用できる。



[改訂第4版]SQLポケットリファレンス

[改訂第4版]SQLポケットリファレンス

  • 作者: 朝井 淳
  • 出版社/メーカー: 技術評論社
  • 発売日: 2017/02/18
  • メディア: 単行本(ソフトカバー)





nice!(0)  コメント(0) 
共通テーマ:携帯コンテンツ



AREarthroid
AREarthroidPro ARで地球を表示するアプリ
AREarthroid ARで地球を表示するアプリ(無料)
AREarthライブ壁紙(無料)
Copyright Atsushi Asai Google+朝井淳

[改訂第4版]SQLポケットリファレンス

[改訂第4版]SQLポケットリファレンス

  • 作者: 朝井 淳
  • 出版社/メーカー: 技術評論社
  • 発売日: 2017/02/18
  • メディア: 単行本(ソフトカバー)

[データベースの気持ちがわかる]SQLはじめの一歩 (WEB+DB PRESS plus)

[データベースの気持ちがわかる]SQLはじめの一歩 (WEB+DB PRESS plus)

  • 作者: 朝井 淳
  • 出版社/メーカー: 技術評論社
  • 発売日: 2015/03/03
  • メディア: 単行本(ソフトカバー)
C言語 ポインタが理解できない理由 [改訂新版] (プログラミングの教科書)

C言語 ポインタが理解できない理由 [改訂新版] (プログラミングの教科書)

  • 作者: 朝井 淳
  • 出版社/メーカー: 技術評論社
  • 発売日: 2011/04/08
  • メディア: 単行本(ソフトカバー)

かんたんAndroidアプリ作成入門 (プログラミングの教科書)

かんたんAndroidアプリ作成入門 (プログラミングの教科書)

  • 作者: 朝井 淳
  • 出版社/メーカー: 技術評論社
  • 発売日: 2013/04/16
  • メディア: 単行本(ソフトカバー)