サブクエリとは SELECT で取得した結果を SELECT文、UPDATE文などで利用することをいいます。
2個以上のテーブルが在って、一方のテーブルを処理するにあたって、他方のテーブルから取得したカラム値をWHERE句の条件に使ったりします。
サブクエリの説明のため以下の2個のテーブルを準備します。 1個は 商品テーブル（マスタ） で、もう1個は商品の 売上数量テーブル です。 商品テーブルは「商品id」「名称」「単価」のカラムがあり、 売上数量テーブルは「テーブルid」「商品id」「売上日」「売上数量」のカラムがあります。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | -- 商品テーブルCREATE TABLE product ( id INT, name VARCHAR(32), price INT);-- 売上数量テーブルCREATE TABLE sales ( id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, id_prd INT, salesdate DATE, volume INT); |
それでは、上記のテーブルに以下の SQL でテストデータを登録します。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | -- 商品テーブルINSERT INTO product(id, name, price) VALUES (1, '鉛筆A', 80), (2, '鉛筆B', 100), (3, 'くれよん', 500), (4, '蛍光ペン', 120), (5, '消しゴム', 150);-- 売上件数テーブルINSERT INTO sales(id_prd, salesdate, volume) VALUES (1, '2024-01-04', 10), (1, '2024-01-07', 15), (2, '2024-01-07', 20), (3, '2024-01-05', 1), (3, '2024-01-10', 2), (3, '2024-01-15', 2), (4, '2024-01-05', 5), (4, '2024-01-14', 3), (4, '2024-01-20', 1), (5, '2024-01-20', 5), (5, '2024-01-22', 10); |
登録されたデータの確認を行います。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | MariaDB [test]> SELECT * FROM product;+----+----------+-------+| id | name | price |+----+----------+-------+| 1 | 鉛筆A | 80 || 2 | 鉛筆B | 100 || 3 | くれよん | 500 || 4 | 蛍光ペン | 120 || 5 | 消しゴム | 150 |+----+----------+-------+5 rows in set (0.00 sec)MariaDB [test]> SELECT * FROM sales;+----+--------+------------+--------+| id | id_prd | salesdate | volume |+----+--------+------------+--------+| 1 | 1 | 2024-01-04 | 10 || 2 | 1 | 2024-01-07 | 15 || 3 | 2 | 2024-01-07 | 20 || 4 | 3 | 2024-01-05 | 1 || 5 | 3 | 2024-01-10 | 2 || 6 | 3 | 2024-01-15 | 2 || 7 | 4 | 2024-01-05 | 5 || 8 | 4 | 2024-01-14 | 3 || 9 | 4 | 2024-01-20 | 1 || 10 | 5 | 2024-01-20 | 5 || 11 | 5 | 2024-01-22 | 10 |+----+--------+------------+--------+11 rows in set (0.01 sec) |
商品テーブルと売上数量テーブルを連結しての商品名称入りの一覧を表示してみます。
1 2 3 4 | SELECT sales.id, sales.id_prd, product.name, sales.salesdate, sales.volume FROM salesLEFT JOIN productON sales.id_prd = product.idORDER BY sales.id; |
この SQL の結果は以下の通りです。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | +----+--------+----------+------------+--------+| id | id_prd | name | salesdate | volume |+----+--------+----------+------------+--------+| 1 | 1 | 鉛筆A | 2024-01-04 | 10 || 2 | 1 | 鉛筆A | 2024-01-07 | 15 || 3 | 2 | 鉛筆B | 2024-01-07 | 20 || 4 | 3 | くれよん | 2024-01-05 | 1 || 5 | 3 | くれよん | 2024-01-10 | 2 || 6 | 3 | くれよん | 2024-01-15 | 2 || 7 | 4 | 蛍光ペン | 2024-01-05 | 5 || 8 | 4 | 蛍光ペン | 2024-01-14 | 3 || 9 | 4 | 蛍光ペン | 2024-01-20 | 1 || 10 | 5 | 消しゴム | 2024-01-20 | 5 || 11 | 5 | 消しゴム | 2024-01-22 | 10 |+----+--------+----------+------------+--------+11 rows in set (0.00 sec) |
■[1個の値を返すサブクエリ]の例
売上数量テーブルからみて売上日付を条件して、「商品id」の最小値をサブクエリで取得し、 その「商品id」で商品テーブルの「id」の条件付けを行います。
1 2 3 4 5 6 | SELECT product.id, product.name FROM productWHERE product.id = ( SELECT MIN(id_prd) FROM sales WHERE salesdate = '2024-01-05'); |
この SQL の結果は以下の通りです。 （「2024-01-05」の売上日で最小の「商品id」は「3」となります。）
1 2 3 4 5 6 | +----+----------+| id | name |+----+----------+| 3 | くれよん |+----+----------+1 row in set (0.00 sec) |
■[複数の値を返すサブクエリで ANY を使う]の例
ANY を使う場合は以下の様な書き方で行います。 ANY は意味合いとして「このサブクエリが返すカラム内の値の "いずれか" に対して比較が TRUE である場合は TRUE を返す」ことを示します。
なお、SOME は ANY のエイリアスなので ANY を SOME に置き換えても同じことになります。1 2 3 4 5 | -- ANY を使う場合のSQLの書き方「条件カラム(式)」「比較演算子」ANY (サブクエリ)-- 条件カラム(式) : 条件式の左側（テーブルカラムや計算式）-- 比較演算子 : = > < >= <= <> != |
それでは実際の例で見てみます。
1 2 3 4 5 6 7 8 | -- 売上件数が10以上の商品SELECT product.id, product.name FROM productWHERE product.id = ANY ( SELECT id_prd FROM sales WHERE volume >= 10)ORDER BY product.id; |
この SQL の結果は以下の様になります。
1 2 3 4 5 6 7 8 | +----+----------+| id | name |+----+----------+| 1 | 鉛筆A || 2 | 鉛筆B || 5 | 消しゴム |+----+----------+3 rows in set (0.00 sec) |
サブクエリの中身だけ実行すると以下の様になります。
1 2 3 | SELECT id_prdFROM salesWHERE volume >= 10; |
この SQL の結果は以下の様になります。
1 2 3 4 5 6 7 8 9 | +--------+| id_prd |+--------+| 1 || 1 || 2 || 5 |+--------+4 rows in set (0.00 sec) |
売上数量テーブルには該当するレコードが4件ありますが、商品テーブルの「id」から見て比較した場合、 「1,1,2,5」の何れかに該当する場合に条件が TRUE になるので商品テーブルの「id:1」は TRUE となります。 よってサブクエリの SQL では3件の商品が対象となります。
4件の値が返るのが少し気持ち悪いのであれば、サブクエリに DISTINCT で重複を抑えることもできます。
今回の ANY を使ったサブクエリですが「= ANY」の部分を「IN」に替えても同様の結果が得られます。
（こちらの方が一般的かも知れません。）
1 2 3 4 5 6 7 | SELECT product.id, product.name FROM productWHERE product.id IN ( SELECT id_prd FROM sales WHERE volume >= 10)ORDER BY product.id; |
■[複数の値を返すサブクエリで ALL を使う]の例
ANY と同様なのですが、ALL を使う場合は以下の様な書き方で行います。 ALL は意味合いとして「このサブクエリが返すカラム内の値の "すべて" に対して比較が TRUE である場合は TRUE を返す」ことを示します。
1 2 3 4 5 | -- ALL を使う場合のSQLの書き方「条件カラム(式)」「比較演算子」ALL (サブクエリ)-- 条件カラム(式) : 条件式の左側（テーブルカラムや計算式）-- 比較演算子 : = > < >= <= <> != |
それでは実際の例で見てみます。
1 2 3 4 5 6 7 8 | -- 売上件数が5未満の商品SELECT product.id, product.name FROM productWHERE product.id >= ALL ( SELECT id_prd FROM sales WHERE volume < 5)ORDER BY product.id; |
この SQL の結果は以下の様になります。
1 2 3 4 5 6 7 | +----+----------+| id | name |+----+----------+| 4 | 蛍光ペン || 5 | 消しゴム |+----+----------+2 rows in set (0.00 sec) |
サブクエリの中身だけ実行すると以下の様になります。
1 2 3 | SELECT id_prdFROM salesWHERE volume < 5; |
この SQL の結果は以下の様になります。
1 2 3 4 5 6 7 8 9 10 | +--------+| id_prd |+--------+| 3 || 3 || 3 || 4 || 4 |+--------+5 rows in set (0.00 sec) |
売上数量テーブルには該当するレコードが5件ありますが、商品テーブルの「id」から見て比較した場合、 「3,3,3,4,4」以上に該当する場合に条件が TRUE になるので商品テーブルの「id:4」「id:5」は TRUE となります。 よってサブクエリの SQL では2件の商品が対象となります。
ここで比較演算子を「>=」から「!=」にしてみます。これはサブクエリの結果以外のものを TRUE に判定します。
1 2 3 4 5 6 7 8 | -- 売上件数が5未満以外の商品SELECT product.id, product.name FROM productWHERE product.id != ALL ( SELECT id_prd FROM sales WHERE volume < 5)ORDER BY product.id; |
サブクエリの中身は「商品id」が「3,4」を返すので、「!=」の演算子であればそれ以外の商品を取得します。
よって以下の様な結果になります。
1 2 3 4 5 6 7 8 | +----+----------+| id | name |+----+----------+| 1 | 鉛筆A || 2 | 鉛筆B || 5 | 消しゴム |+----+----------+3 rows in set (0.00 sec) |
上記の ALL を使ったサブクエリですが「!= ALL」の部分を「NOT IN」に替えても同様の結果が得られます。
（こちらの方が一般的かも知れません。）
1 2 3 4 5 6 7 | SELECT product.id, product.name FROM productWHERE product.id NOT IN ( SELECT id_prd FROM sales WHERE volume < 5)ORDER BY product.id; |
コメント