現実の業務では、関連する２つのテーブルの一方から値を引き出してきて、それをもう一方のテーブルに対する操作の条件に用いる――という処理がよく登場します。その場合、本来ならリレーションを設定しなければなりません。しかし、リレーションを設定しないでそれを実現する便利な方法があります。
- 他のテーブルを参照する構造 -
まず、今回使用する３つのテーブルのフィールド構成を再確認しておきましょう。表2を参照してください。
表2：３つのテーブルのフィールド構成
| テーブル |
： |
フィールド |
| 商品_mr |
： |
商品ID 品名 仕入先ID 販売単価 仕入単価 |
| 仕入先_mr |
： |
仕入先ID 仕入先名 |
| 在庫_mr |
： |
商品ID 在庫数 |
このような状態で、例えば
「菊谷家具」という仕入先から仕入れている商品の仕入単価が10％アップした場合の処理を考えてみましょう。
仕入単価は「商品_mr」テーブルのフィールドなので、UPDATE命令で
仕入単価 * 1.1
という式を与えればよいことは分かります。
菊谷家具の仕入先IDが“000003”であることが分かっていれば、WHERE句を使って次のようなSQLが書けます。
UPDATE 商品_mr SET 仕入単価 = 仕入単価 * 1.1 WHERE 仕入先ID=3
ところが、ここでは仕入先名である“菊谷家具”は明確になっていても、その「仕入先ID」は不明です。
UPDATE命令の対象であるテーブル「商品mr」には「仕入先ID」のみが記録され、仕入先の名称は記録されていません。仕入先の名称のみが分かっていて、その仕入先IDが分からない場合には、上のようなSQLは使えないのです。
- ２段階の処理で対処する -
リレーションを設定して運用することが前提のデータベースでは、このような状況がよく生じます。ここでは、テーブル「仕入先_mr」のレコードを参照し、そこから「仕入先ID」フィールドの値を取得しなければなりません。そのためには、以下の２段階の処理が必要です。
| 1. |
仕入先名「菊谷家具」の「仕入先ID」を知る
これには、WHERE句を伴うSELECT命令が使えます。
SELECT 仕入先ID FROM 仕入先_mr WHERE 仕入先名 Like '菊谷家具' |
| 2. |
上記SQLで得られた「仕入先ID」をUPDATE命令のWHERE句の条件に指定する
UPDATE 商品_mr SET 仕入単価 = 仕入単価*1.1
WHERE 仕入先ID =
(SELECT 仕入先ID FROM 仕入先_mr WHERE 仕入先名 Like '菊谷家具') |
※サンプルのクエリファイル“ex02.sql”では、「商品_mr」の代わりに同じ内容を保持した「商品_dmy」を対象としています
このように、あるSQL文のWHERE句に設定する条件式に、SELECT命令を使ったSQLを指定できます。WHERE句を伴うSELECT命令によるレコードの絞り込みを「クエリ（qery：問い合わせ）」と呼び、その結果を別のSQLのWHERE句で条件式にできるのです。
この、条件式とするSELECT文を「サブクエリ」と言います。
- 参照先テーブルから値を引き出せる -
サブクエリは、UPDATEの他、DELETEやINSERT、SELECTなどの命令で使えます。単純にレコードを抽出するだけのSELECT命令で使う局面はそう多くないでしょう（抽出だけなら、ビューを使った方が簡単です）。リレーションの設定された状態で、UPDATEやDELETEなどを使って既存のレコードから処理対象のレコードを絞り込むときに役立ちます。
例えば、
「菊谷家具」から仕入れていた商品をすべて削除する
という場合なら、以下のようなSQLを記述します。
DELETE 商品_mr WHERE 仕入先ID =
(SELECT 仕入先ID FROM 仕入先_mr WHERE 仕入先名 Like '菊谷家具')
※サンプルのクエリファイル“ex02.sql”では、「商品_mr」の代わりに同じ内容を保持した「商品_dmy」を対象としています
- 実行にはダミーのテーブルを -
もうお分かりとは思いますが、UPDATEやDELETE命令を実行すると、テーブルの内容が書き換えられます。特に上のDELETE命令を実行すると、「商品_mr」テーブルから複数のレコードが削除されてしまうため、注意が必要です。
令を実行すると、「商品_mr」テーブルから複数のレコードが削除されてしまうため、注意が必要です。
ここまで読んできていただいたみなさんに今さら説明する必要はないと思いますが、実際にサンプルのSQLを試す場合には、「商品_mr」と同じ内容の「商品_dmy」テーブルを作り、それを使ってください。
既存テーブルの別名コピーを作るには、SELECT～INTO命令を使います。クエリアナライザで以下のSQLを実行してください。
SELECT * INTO 商品_dmy FROM 商品_mr
このSQLを記録したクエリファイルは、“ex04.sql”という名前でサンプルファイルの中に入っています。
今回紹介したサブクエリは、ある処理の条件に「別のテーブルから読み出した値」を設定できる機能です。
本文で紹介したSQLを見れば、そこにリレーションを設定する命令の記述がないことに気付くでしょう。こういった処理は、本来２つのテーブルにリレーションを設定するためのSQLを記述したり、データベース側でダイヤグラムに基づいたビューを作ったりしなければなりません。しかし、サブクエリを使えば、そういった面倒な記述や設定をしないままに、関連するテーブルから値を引き出して利用できます。
但し、リレーションを意識しないで別テーブルの値を参照し利用できるというメリットは、裏返せばまったく関連するはずのないテーブル同士をあたかも関連しているように扱うこともできてしまう――という問題も引き起こします。
サブクエリは、データベースの構造を正しく把握しているプログラマーが、あくまで簡単・単純な処理のために用いるものであることを理解して、頼りすぎない程度に活用しましょう。
 サンプルファイル (LZH形式 587 KB)
 |
|
|
