サブクエリとは SELECT で取得した結果を SELECT文、UPDATE文などで利用することをいいます。
2個以上のテーブルが在って、一方のテーブルを処理するにあたって、他方のテーブルから取得したカラム値をWHERE句の条件に使ったりします。
サブクエリの説明のため以下の2個のテーブルを準備します。 1個は 商品テーブル（マスタ） で、もう1個は商品の 売上数量テーブル です。 商品テーブルは「商品id」「名称」「単価」のカラムがあり、 売上数量テーブルは「テーブルid」「商品id」「売上日」「売上数量」のカラムがあります。
-- 商品テーブル
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 でテストデータを登録します。
-- 商品テーブル
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);
登録されたデータの確認を行います。
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)
商品テーブルと売上数量テーブルを連結しての商品名称入りの一覧を表示してみます。
SELECT sales.id, sales.id_prd, product.name, sales.salesdate, sales.volume FROM sales LEFT JOIN product ON sales.id_prd = product.id ORDER BY sales.id;
この SQL の結果は以下の通りです。
+----+--------+----------+------------+--------+ | 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」の条件付けを行います。
SELECT product.id, product.name FROM product
WHERE product.id = (
SELECT MIN(id_prd)
FROM sales
WHERE salesdate = '2024-01-05'
);
この SQL の結果は以下の通りです。 （「2024-01-05」の売上日で最小の「商品id」は「3」となります。）
+----+----------+ | id | name | +----+----------+ | 3 | くれよん | +----+----------+ 1 row in set (0.00 sec)
■[複数の値を返すサブクエリで ANY を使う]の例
ANY を使う場合は以下の様な書き方で行います。 ANY は意味合いとして「このサブクエリが返すカラム内の値の "いずれか" に対して比較が TRUE である場合は TRUE を返す」ことを示します。
なお、SOME は ANY のエイリアスなので ANY を SOME に置き換えても同じことになります。-- ANY を使う場合のSQLの書き方 「条件カラム(式)」「比較演算子」ANY (サブクエリ) -- 条件カラム(式) : 条件式の左側（テーブルカラムや計算式） -- 比較演算子 : = > < >= <= <> !=
それでは実際の例で見てみます。
-- 売上件数が10以上の商品
SELECT product.id, product.name FROM product
WHERE product.id = ANY (
SELECT id_prd
FROM sales
WHERE volume >= 10
)
ORDER BY product.id;
この SQL の結果は以下の様になります。
+----+----------+ | id | name | +----+----------+ | 1 | 鉛筆A | | 2 | 鉛筆B | | 5 | 消しゴム | +----+----------+ 3 rows in set (0.00 sec)
サブクエリの中身だけ実行すると以下の様になります。
SELECT id_prd FROM sales WHERE volume >= 10;
この SQL の結果は以下の様になります。
+--------+ | 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」に替えても同様の結果が得られます。
（こちらの方が一般的かも知れません。）
SELECT product.id, product.name FROM product
WHERE product.id IN (
SELECT id_prd
FROM sales
WHERE volume >= 10
)
ORDER BY product.id;
■[複数の値を返すサブクエリで ALL を使う]の例
ANY と同様なのですが、ALL を使う場合は以下の様な書き方で行います。 ALL は意味合いとして「このサブクエリが返すカラム内の値の "すべて" に対して比較が TRUE である場合は TRUE を返す」ことを示します。
-- ALL を使う場合のSQLの書き方 「条件カラム(式)」「比較演算子」ALL (サブクエリ) -- 条件カラム(式) : 条件式の左側（テーブルカラムや計算式） -- 比較演算子 : = > < >= <= <> !=
それでは実際の例で見てみます。
-- 売上件数が5未満の商品 SELECT product.id, product.name FROM product WHERE product.id >= ALL ( SELECT id_prd FROM sales WHERE volume < 5 ) ORDER BY product.id;
この SQL の結果は以下の様になります。
+----+----------+ | id | name | +----+----------+ | 4 | 蛍光ペン | | 5 | 消しゴム | +----+----------+ 2 rows in set (0.00 sec)
サブクエリの中身だけ実行すると以下の様になります。
SELECT id_prd FROM sales WHERE volume < 5;
この SQL の結果は以下の様になります。
+--------+ | 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 に判定します。
-- 売上件数が5未満以外の商品 SELECT product.id, product.name FROM product WHERE product.id != ALL ( SELECT id_prd FROM sales WHERE volume < 5 ) ORDER BY product.id;
サブクエリの中身は「商品id」が「3,4」を返すので、「!=」の演算子であればそれ以外の商品を取得します。
よって以下の様な結果になります。
+----+----------+ | id | name | +----+----------+ | 1 | 鉛筆A | | 2 | 鉛筆B | | 5 | 消しゴム | +----+----------+ 3 rows in set (0.00 sec)
上記の ALL を使ったサブクエリですが「!= ALL」の部分を「NOT IN」に替えても同様の結果が得られます。
（こちらの方が一般的かも知れません。）
SELECT product.id, product.name FROM product
WHERE product.id NOT IN (
SELECT id_prd
FROM sales
WHERE volume < 5
)
ORDER BY product.id;
コメント