初詣合格祈願ＳＱＬ講座 複数の表の連携９
EXISTS 述語を使った副問い合わせ（相関副問い合わせ）
副問い合わせの中に相関副問い合わせという方法があります。これは、主問い合わせの結果をチェックするというものです。相関副問い合わせでは主問い合わせの表の1行ごとに副問い合わせを実行します。そして、EXISTSを指定された副問い合わせでは、主問い合わせで指定されたデータが存在すれば真を返し、存在しなければ偽を返し、真の行だけが抽出されます。
まず、準備としてEXISTS述語を指定したSELECT文の実行結果が分かりやすいように商品表につぎの2つの行を追加してください。
| 売上No | 連番 | 商品CD | 数量 |
|---|---|---|---|
| 1 | 1 | 10 | 10 |
| 1 | 2 | 30 | 7 |
| 1 | 3 | 60 | 25 |
| 2 | 1 | 20 | 15 |
| 2 | 2 | 40 | 3 |
| 3 | 1 | 30 | 8 |
| 3 | 2 | 40 | 30 |
| 3 | 3 | 50 | 12 |
| 3 | 4 | 60 | 50 |
| 4 | 1 | 20 | 21 |
| 4 | 2 | 40 | 21 |
| 5 | 1 | 10 | 17 |
| 5 | 2 | 20 | 5 |
<例題>
売上のあった商品名を抽出する。
SELECT 商品CD,商品名
FROM 商品表
WHERE EXISTS
(SELECT * FROM 売上明細 WHERE 売上明細.商品CD = 商品表.商品CD)
売上のあった商品とは、売上明細に存在する商品のことです。つまり、商品表の商品のうち売上明細に存在する商品を抽出すればよいのです。
| 商品CD | 商品名 |
|---|---|
| 10 | 鉛筆 |
| 20 | 消しゴム |
| 30 | シャープペン |
| 40 | ボールペン |
| 50 | 色鉛筆 |
| 60 | ノート |
売上明細に存在する商品CDと商品名が抽出されている。商品CDが70の手帳と、商品CDが80のホッチキスは売上明細に存在しないので抽出されない
相関副問い合わせの動作を見ていきましょう。まず主問い合わせを実行します。実行結果はつぎのとおりです。
1行目について、副問い合わせ(SELECT * FROM 売上明細 WHERE 売上明細.商品CD = 商品表.商品CD)を実行し、一致する行があるかどうかをチェックします。この場合は、1行目の商品CDの値は"10"ですから、SELECT * FROM 売上明細 WHERE 売上明細.商品CD = 10を実行します。一致する行が存在するので真が返され、抽出されます。EXISTS述語を指定すると、副問い合わせで主問い合わせとは別の表を指定し、チェックすることができます。
同様にして、2行目以降も実行します。新しく追加した商品CDが70の手帳と、商品CDが80のホッチキスは売上明細に存在しないので抽出されません。これとは逆に、存在しないときを真にするときは、NOT EXISTSを使います。つぎの例題は、NOT EXISTSを使って商品表より売上の無かった商品を抽出します。
<例題>
売上のなかった商品名を抽出する。
SELECT 商品CD,商品名
FROM 商品表
WHERE NOT EXISTS
(SELECT * FROM 売上明細 WHERE 売上明細.商品CD = 商品表.商品CD)
| 商品CD | 商品名 |
|---|---|
| 70 | 手帳 |
| 80 | ホッチキス |
NOT EXISTSが指定されているので売上明細に存在しない商品CDと商品名が抽出されている。
お正月に関する花や植物を１７種紹介いたします。