MySQL のSELECT文ではあるテーブルをメインとして扱い、それに必要なテーブルを連結して一覧データを取得します。
連結するテーブルが存在するかどうかを条件に含める場合は EXISTS を使います。
また、テーブルが存在しない場合は NOT EXISTS を使います。
それでは以下の記事にありましたが、売上データの一覧リストの取得を行う SELECT文 を参考にして EXISTS を考えてみます。
⇒MySQL マスタテーブルの作成と参照に関する考察について(CREATE TABLE、AUTOINCREMENT、JOIN、LEFT JOIN)
■売上データの一覧リストの取得について
以下の SELECT文 は商品マスタの削除フラグに関わらず、売上テーブルの一覧を取得しています。
なお、商品マスタの削除フラグを表示する様にしています。
SELECT
tt_sales.no AS '売上no',
tt_sales.id_prd AS '商品id',
tm_prod.code AS '商品コード',
tm_prod.name AS '商品名',
tm_prod.del AS '削除フラグ',
tt_sales.volume AS '数量',
tt_sales.price AS '単価',
tt_sales.amount AS '金額'
FROM tt_sales
INNER JOIN tm_prod
ON tm_prod.id = tt_sales.id_prd
ORDER BY tt_sales.no;
この SQL の結果は以下の通りです。
売上テーブルと商品マスタは「商品id」のみの結合のため、削除フラグに関わらず一覧表示されます。
+--------+--------+------------+--------------+------------+------+------+------+ | 売上no | 商品id | 商品コード | 商品名 | 削除フラグ | 数量 | 単価 | 金額 | +--------+--------+------------+--------------+------------+------+------+------+ | 1 | 1 | pen01 | 鉛筆HB | 0 | 10 | 80 | 800 | | 2 | 4 | cre01 | くれよん12色 | 1 | 1 | 1000 | 1000 | | 3 | 6 | pen10 | 蛍光ペン | 0 | 2 | 120 | 240 | | 4 | 7 | pen11 | 蛍光ペン2 | 1 | 3 | 130 | 390 | | 5 | 1 | pen01 | 鉛筆HB | 0 | 15 | 80 | 1200 | | 6 | 2 | pen02 | 鉛筆B | 0 | 10 | 100 | 1000 | | 7 | 5 | cre02 | くれよん24色 | 0 | 2 | 2000 | 4000 | | 8 | 1 | pen01 | 鉛筆HB | 0 | 20 | 80 | 1600 | | 9 | 3 | pen03 | 鉛筆B2 | 0 | 1 | 100 | 100 | | 10 | 4 | cre01 | くれよん12色 | 1 | 2 | 1000 | 2000 | | 11 | 2 | pen02 | 鉛筆B | 0 | 2 | 100 | 200 | | 12 | 6 | pen10 | 蛍光ペン | 0 | 5 | 120 | 600 | | 13 | 7 | pen11 | 蛍光ペン2 | 1 | 3 | 130 | 390 | | 14 | 8 | era01 | 消しゴム | 0 | 1 | 150 | 150 | | 15 | 5 | cre02 | くれよん24色 | 0 | 4 | 2000 | 8000 | | 16 | 8 | era01 | 消しゴム | 0 | 10 | 150 | 1500 | | 17 | 9 | era02 | 消しゴム2 | 0 | 10 | 200 | 2000 | +--------+--------+------------+--------------+------------+------+------+------+ 17 rows in set (0.00 sec)
■EXISTSを使った一覧リストの取得について
上記の SELECT文 に EXISTS を使って削除フラグの条件付けを行います。
なお EXISTSの書き方は以下の様に WHERE の中で記述します。 EXISTS の括弧"(...)"で囲まれた部分は条件付けとなるサブクエリを記述します。 このサブクエリが少なくとも1行を返す場合に「true」で、返さない場合は「false」となります。
SELECT [table1.カラムリスト] FROM table1 WHERE EXISTS ( SELECT * FROM table2 )
先ほどの一覧SQLで商品マスタの「削除フラグ = 0」の場合（商品マスタが有効）、売上テーブルの内容を表示する SQL を以下に記します。
以下の SQL ですが EXISTS の説明のため、リストの項目に商品マスタのカラムは参照していません。
SELECT
tt_sales.no AS '売上no',
tt_sales.id_prd AS '商品id',
tt_sales.volume AS '数量',
tt_sales.price AS '単価',
tt_sales.amount AS '金額'
FROM tt_sales
WHERE EXISTS ( -- 削除フラグが[0]の場合[true]
SELECT 1 FROM tm_prod
WHERE tm_prod.id = tt_sales.id_prd
AND tm_prod.del = 0
)
ORDER BY tt_sales.no;
EXISTS のサブクエリで SELECT 1 と記述していますが、 この SELECT の中身は何でもよく SELECT * でも SELECT '1' でもよく MySQL では無視される様です。
この SQL の実行結果は以下の通りです。
+--------+--------+------+------+------+ | 売上no | 商品id | 数量 | 単価 | 金額 | +--------+--------+------+------+------+ | 1 | 1 | 10 | 80 | 800 | | 3 | 6 | 2 | 120 | 240 | | 5 | 1 | 15 | 80 | 1200 | | 6 | 2 | 10 | 100 | 1000 | | 7 | 5 | 2 | 2000 | 4000 | | 8 | 1 | 20 | 80 | 1600 | | 9 | 3 | 1 | 100 | 100 | | 11 | 2 | 2 | 100 | 200 | | 12 | 6 | 5 | 120 | 600 | | 14 | 8 | 1 | 150 | 150 | | 15 | 5 | 4 | 2000 | 8000 | | 16 | 8 | 10 | 150 | 1500 | | 17 | 9 | 10 | 200 | 2000 | +--------+--------+------+------+------+ 13 rows in set (0.00 sec)
商品マスタを連結して商品名などを表示する SQL は以下の様になります。
SELECT
tt_sales.no AS '売上no',
tt_sales.id_prd AS '商品id',
tm_prod.code AS '商品コード',
tm_prod.name AS '商品名',
tm_prod.del AS '削除フラグ',
tt_sales.volume AS '数量',
tt_sales.price AS '単価',
tt_sales.amount AS '金額'
FROM tt_sales
INNER JOIN tm_prod
ON tm_prod.id = tt_sales.id_prd
WHERE EXISTS (
SELECT 1 FROM tm_prod
WHERE tm_prod.id = tt_sales.id_prd
AND tm_prod.del = 0
)
ORDER BY tt_sales.no;
■NOT EXISTSを使った一覧リストの取得について
上記の EXISTS の部分を NOT EXISTS とすればリストされる条件が反転されて、 削除済みの商品マスタの売上テーブルのデータが表示されます。
SELECT
tt_sales.no AS '売上no',
tt_sales.id_prd AS '商品id',
tt_sales.volume AS '数量',
tt_sales.price AS '単価',
tt_sales.amount AS '金額'
FROM tt_sales
WHERE NOT EXISTS ( -- 削除フラグが[1]の場合[true]
SELECT * FROM tm_prod
WHERE tm_prod.id = tt_sales.id_prd
AND tm_prod.del = 0
)
ORDER BY tt_sales.no;
結果は以下の様になります。削除された商品マスタの売上データのみ表示されます。
+--------+--------+------+------+------+ | 売上no | 商品id | 数量 | 単価 | 金額 | +--------+--------+------+------+------+ | 2 | 4 | 1 | 1000 | 1000 | | 4 | 7 | 3 | 130 | 390 | | 10 | 4 | 2 | 1000 | 2000 | | 13 | 7 | 3 | 130 | 390 | +--------+--------+------+------+------+ 4 rows in set (0.16 sec)
EXISTS句 ではサブクエリーの書き方ではより複雑な条件付けができると思います。
コメント