【データベース】「SQLの実践」簡単速習‼【⑥サブクエリー/MySQL】

こんにちはヤク学長です。
データサイエンティスト兼ファーマシストで、アルゴリズムやBI開発を行っています。

本記事の目的は、「SQLの基本操作を知る」ことを目的としています。

【本記事のもくじ】

まず、「SQL」に真剣に取り組むための概要を解説します。
下記の方法で、簡単に概要を抑えることができます。

１０．サブクエリー

それでは、上から順番に見ていきます。
なお、本上記の方法を順番に抑えれば成果が出ます。

記事の内容は「転載 & 引用OK」問題ありません。

1 １０．サブクエリー

１０．サブクエリー

副問い合わせとは

MySQLで副問い合わせとは、クエリの中で別のSELECT文を使用することで、より複雑な検索条件や結果を得ることができる機能です。副問い合わせは、主に以下の2つの方法で使用されます。

WHERE句での副問い合わせ副問い合わせを使用して、クエリのWHERE句に検索条件を追加することができます。以下は、ordersテーブルから金額が最も高い注文情報を取得する例です。

SELECT *
FROM orders
WHERE amount = (
　　SELECT MAX(amount)
　　FROM orders
);

この例では、ordersテーブルの中で、amount列が最大値となるレコードを副問い合わせで取得し、そのレコードの情報をWHERE句で取得しています。

FROM句での副問い合わせ副問い合わせを使用して、クエリのFROM句にサブクエリを指定することができます。以下は、ordersテーブルとorder_itemsテーブルを結合し、それぞれの注文ごとに商品数と合計金額を算出する例です。

SELECT o.order_id, o.order_date, COUNT(oi.item_id) as item_count, SUM(oi.price) as total_price
FROM orders o
JOIN (
　　SELECT order_id, item_id, price
　　FROM order_items
) oi ON o.order_id = oi.order_id
GROUP BY o.order_id, o.order_date;

この例では、order_itemsテーブルの情報を副問い合わせで取得し、ordersテーブルと結合しています。そして、注文ごとに商品数と合計金額を算出しています。

副問い合わせを使用することで、複雑な検索条件や結果を得ることができるため、SQLの中でも重要な機能の一つです。

IN演算子と集計関数を組み合わせる

MySQLで副問い合わせを使用して、IN演算子と集計関数を組み合わせることができます。

以下は、productsテーブルとordersテーブルを結合し、特定の商品が注文された回数と、その商品の総売上額を取得する例です。

SELECT p.product_id, p.product_name,
(SELECT COUNT(*) FROM orders o WHERE o.product_id = p.product_id) AS order_count,
(SELECT SUM(quantity * price) FROM orders o WHERE o.product_id = p.product_id) AS total_sales
FROM products p
WHERE p.product_id IN (1, 2, 3);

この例では、副問い合わせを使用してordersテーブルから特定の商品の注文数と総売上額を取得しています。そして、IN演算子を使用して、productsテーブルとordersテーブルを結合し、特定の商品の情報と注文数、総売上額を取得しています。

このように、副問い合わせを使用することで、より複雑な検索条件や集計を行うことができます。ただし、副問い合わせはクエリの実行時間を増やす原因となるため、必要な場合に限定して使用することが望ましいです。

SELECT文の中に書く

MySQLで副問い合わせを使用して、SELECT文の中に書くことができます。

以下は、ordersテーブルから顧客ごとに最大の注文金額を取得する例です。

SELECT customer_id,
(SELECT MAX(amount) FROM orders o WHERE o.customer_id = c.customer_id) AS max_amount
FROM customers c;

この例では、副問い合わせを使用して、ordersテーブルから顧客ごとに最大の注文金額を取得しています。そして、SELECT文の中で副問い合わせを使用して、customersテーブルとordersテーブルを関連付け、各顧客ごとに最大の注文金額を取得しています。

副問い合わせを使用することで、クエリの中で複雑な検索や集計を行うことができます。ただし、副問い合わせはクエリの実行時間を増やす原因となるため、必要な場合に限定して使用することが望ましいです。

CASE文とともに使う

MySQLで副問い合わせを使用して、CASE文とともに使うことができます。

以下は、ordersテーブルから顧客ごとに最大の注文金額を取得し、その金額に応じてランクを付ける例です。

SELECT customer_id,
CASE
　　WHEN max_amount >= 10000 THEN 'プラチナ会員'
　　WHEN max_amount >= 5000 THEN 'ゴールド会員'
ELSE 'シルバー会員'
END AS rank
FROM (
　　SELECT customer_id, (SELECT MAX(amount) FROM orders o WHERE o.customer_id = c.customer_id) AS max_amount
　　FROM customers c
) t;

この例では、副問い合わせを使用して、ordersテーブルから顧客ごとに最大の注文金額を取得しています。そして、CASE文を使用して、最大の注文金額に応じてランクを付けています。副問い合わせの結果をサブクエリで定義し、メインクエリの中でCASE文を使用することで、より複雑な処理を行うことができます。

このように、副問い合わせとCASE文を組み合わせることで、より複雑な条件分岐や処理を行うことができます。ただし、副問い合わせはクエリの実行時間を増やす原因となるため、必要な場合に限定して使用することが望ましいです。

INSERT INTO SELECT

MySQLで、INSERT INTO SELECT文を使用することで、SELECT文の結果を別のテーブルに挿入することができます。

以下は、ordersテーブルから特定の商品を注文した顧客の注文情報を、別のテーブルに挿入する例です。

INSERT INTO new_orders (order_id, customer_id, product_id, quantity, price)
SELECT order_id, customer_id, product_id, quantity, price
FROM orders
WHERE product_id = 1;

この例では、ordersテーブルからproduct_idが1の商品を注文した顧客の注文情報をSELECT文で取得し、new_ordersテーブルに挿入しています。SELECT文で取得した列と、INSERT INTO文で指定した列が一致しているため、データを挿入することができます。

INSERT INTO SELECT文を使用することで、複数のテーブル間でデータをコピーしたり、一部のデータを別のテーブルに抽出することができます。ただし、挿入先のテーブルとSELECT文で取得するテーブルの列が一致している必要があります。また、挿入先のテーブルに制約がある場合は、制約に違反しないように注意する必要があります。

CLEATE TABLE SELECT

MySQLで、CREATE TABLE SELECT文を使用することで、SELECT文の結果を別のテーブルにコピーし、新しいテーブルを作成することができます。

以下は、ordersテーブルから特定の商品を注文した顧客の注文情報を、新しいテーブルにコピーし、新しいテーブルを作成する例です。

CREATE TABLE new_orders AS
SELECT order_id, customer_id, product_id, quantity, price
FROM orders
WHERE product_id = 1;

この例では、ordersテーブルからproduct_idが1の商品を注文した顧客の注文情報をSELECT文で取得し、新しいテーブルnew_ordersを作成して、そのテーブルにデータをコピーしています。CREATE TABLE AS文を使用することで、新しいテーブルを作成し、同時にデータを挿入することができます。

CREATE TABLE SELECT文を使用することで、テーブルの構造を保持したまま、データをコピーすることができます。ただし、コピー元のテーブルと新しいテーブルの列が一致している必要があります。また、新しいテーブルに制約を設定する場合は、別途ALTER TABLE文を使用して設定する必要があります。

というわけで、今回は以上です。大変大変お疲れ様でした。
引き続きで、徐々に発信していきます。

コメントや感想を受け付けています。ちょっとした感想でもいいので嬉しいです。

それでは、以上です。