━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
■第31回 複数テーブルからの選択
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
SQLシリーズ、しばらく続きます。
今回は複数テーブルの結合についてです。
そんなの知ってるよという方も多いとは思いますが、引っかかりやすい落と
し穴もありますので、理解しているかチェックしてみてください。
実例を通して紹介しますので、以下のサンプルテーブルをご覧ください。
・取引明細
---------------------------------------------
| 取引番号 | 顧客コード | 商品コード | 数量 |
-----------+------------+------------+-------
| 00000001 | 0001 | A001 | 10 |
-----------+------------+------------+-------
| 00000002 | 0001 | B001 | 5 |
-----------+------------+------------+-------
| 00000003 | 0002 | A002 | 8 |
-----------+------------+------------+-------
| 00000004 | 0003 | A001 | 25 |
-----------+------------+------------+-------
| 00000005 | 0001 | B002 | 15 |
---------------------------------------------
・商品
----------------------------------
| 商品コード | 商品名 | 単価 |
-------------+------------+-------
| A001 | 抹茶ケーキ | 200 |
-------------+------------+-------
| A002 | モンブラン | 250 |
-------------+------------+-------
| B001 | クッキー | 80 |
-------------+------------+-------
| B002 | ビスケット | 70 |
-------------+------------+-------
| C001 | マシュマロ | 100 |
----------------------------------
●複数テーブルからの選択
結合するときは、JOIN句を使います。
例として、取引明細と商品を結合して選択すると、以下のようになります。
SELECT
取引明細.取引番号,
取引明細.商品コード,
商品.商品名,
商品.単価,
取引明細.数量,
商品.単価 * 取引明細.数量 AS 合計
FROM
取引明細 INNER JOIN 商品
ON 取引明細.商品コード = 商品.商品コード
-----------------------------------------------------------
| 取引番号 | 商品コード | 商品名 | 単価 | 数量 | 合計 |
-----------+------------+------------+------+------+-------
| 00000001 | A001 | 抹茶ケーキ | 200 | 10 | 2000 |
-----------+------------+------------+------+------+-------
| 00000002 | B001 | クッキー | 80 | 5 | 400 |
-----------+------------+------------+------+------+-------
| 00000003 | A002 | モンブラン | 250 | 8 | 2000 |
-----------+------------+------------+------+------+-------
| 00000004 | A001 | 抹茶ケーキ | 200 | 25 | 5000 |
-----------+------------+------------+------+------+-------
| 00000005 | B002 | ビスケット | 70 | 15 | 1050 |
-----------------------------------------------------------
結合条件はONの後に記述します。
この例では、取引明細の商品コードと商品の商品コードが一致するレコード
同士が結合しますね。
●外部結合
結合対象のレコードが存在しなくても、外部結合することでNULL値として選
択することが可能です。
例として、先ほどのSQLを商品から見ると、C001の取引明細が存在しないた
め、結合することができませんが、OUTER JOINを使用すると以下のように選
択することができます。
SELECT
取引明細.取引番号,
取引明細.商品コード,
商品.商品名,
商品.単価,
取引明細.数量,
商品.単価 * 取引明細.数量 AS 合計
FROM
商品 LEFT OUTER JOIN 取引明細
ON 取引明細.商品コード = 商品.商品コード
ORDER BY
商品コード, 取引番号
-----------------------------------------------------------
| 取引番号 | 商品コード | 商品名 | 単価 | 数量 | 合計 |
-----------+------------+------------+------+------+-------
| 00000001 | A001 | 抹茶ケーキ | 200 | 10 | 2000 |
-----------+------------+------------+------+------+-------
| 00000004 | A001 | 抹茶ケーキ | 200 | 25 | 5000 |
-----------+------------+------------+------+------+-------
| 00000003 | A002 | モンブラン | 250 | 8 | 2000 |
-----------+------------+------------+------+------+-------
| 00000002 | B001 | クッキー | 80 | 5 | 400 |
-----------+------------+------------+------+------+-------
| 00000005 | B002 | ビスケット | 70 | 15 | 1050 |
-----------+------------+------------+------+------+-------
| NULL | C001 | マシュマロ | 100 | NULL | NULL |
-----------------------------------------------------------
ORDER BYは、表示順を指定する命令です。
この例では、商品コードの昇順、さらに取引番号の昇順で表示順を指定して
います。
●外部結合の落とし穴
結合条件はON、選択条件はWHEREに書きますが、逆にすることも可能です。
つまり、選択条件をON、結合条件をWHEREに書くこともできます。
結合条件をWHEREに書いた場合、外部結合できないという不便さがあります
ので、あまり使う人はいないのですが、選択条件をONに書いているケースは
比較的目にすることが多いです。
そうしている理由として、結合前に対象レコードを絞り込んでおいた方が実
行速度が速くなるということを挙げられることが多く、データベースの種類
によっては確かに速くなるようです。
SELECT
取引明細.取引番号,
取引明細.商品コード,
商品.商品名,
商品.単価,
取引明細.数量,
商品.単価 * 取引明細.数量 AS 合計
FROM
取引明細 INNER JOIN 商品
ON (取引明細.商品コード = 商品.商品コード
AND 商品.単価 > 100)
ORDER BY
取引明細.取引番号
-----------------------------------------------------------
| 取引番号 | 商品コード | 商品名 | 単価 | 数量 | 合計 |
-----------+------------+------------+------+------+-------
| 00000001 | A001 | 抹茶ケーキ | 200 | 10 | 2000 |
-----------+------------+------------+------+------+-------
| 00000003 | A002 | モンブラン | 250 | 8 | 2000 |
-----------+------------+------------+------+------+-------
| 00000004 | A001 | 抹茶ケーキ | 200 | 25 | 5000 |
-----------------------------------------------------------
この例では、商品単価が100円未満を対象としています。
WHEREに書いても同様の結果が得られます。
では、以下のSQLではどうでしょうか。
SELECT
取引明細.取引番号,
取引明細.商品コード,
商品.商品名,
商品.単価,
取引明細.数量,
商品.単価 * 取引明細.数量 AS 合計
FROM
取引明細 LEFT OUTER JOIN 商品
ON (取引明細.商品コード = 商品.商品コード
AND 商品.単価 > 100)
ORDER BY
取引明細.取引番号
-----------------------------------------------------------
| 取引番号 | 商品コード | 商品名 | 単価 | 数量 | 合計 |
-----------+------------+------------+------+------+-------
| 00000001 | A001 | 抹茶ケーキ | 200 | 10 | 2000 |
-----------+------------+------------+------+------+-------
| 00000002 | B001 | NULL | NULL | 5 | NULL |
-----------+------------+------------+------+------+-------
| 00000003 | A002 | モンブラン | 250 | 8 | 2000 |
-----------+------------+------------+------+------+-------
| 00000004 | A001 | 抹茶ケーキ | 200 | 25 | 5000 |
-----------+------------+------------+------+------+-------
| 00000005 | B002 | NULL | NULL | 15 | NULL |
-----------------------------------------------------------
先ほどとほとんど同じSQLなのに、結果がずいぶん違いますね。
選択条件を書いたはずなのに、結合されてしまっています。
しかもNULLで。
なぜかと言いますと、せっかく選択条件で絞り込んでも、絞り込んだものを
外部結合してしまうのでNULL値として選択されてしまうのです。
選択条件をON、WHEREのどちらに書くかは様々なケースがあるので、どちら
が正しいとは言えませんが、外部結合の際には注意が必要ということを覚え
ておきましょう。
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━