SQLの副問合せについて、サンプルコードつきで紹介しています。
SQLの副問合せと応用例のサンプルコード
SQLの副問合せ（サブクエリ）とは、別のクエリの結果を利用したクエリを指します。以下はstaffsテーブルからage=45のレコードのidを抽出するサブクエリを利用しています。
SELECT DISTINCT jobid FROM rolls WHERE staffid IN (SELECT id FROM staffs WHERE age = 45); +------+ |jobid | +------+ | 1 | | 2 | +------+
【関連記事】
▶SQLの副問い合わせ（サブクエリ）とは？概要・種類・使い方を解説
この記事では、サブクエリを使った応用例をサンプルコードをまじえて紹介します。
サブクエリを使ったテーブルの自己結合
サブクエリを使って、同一のテーブルでの結合（自己結合）が可能です。以下は、employees（社員テーブル）から、同一のhire_date（雇用日）のデータが存在するレコードを抽出する自己結合の例です。
同じテーブルemployeesにメインクエリではx、サブクエリではyと別の名前をつけ、別テーブルのように扱っています。
SELECT *
FROM employees x
WHERE EXISTS (SELECT *
FROM employees y
WHERE x.hire_date = y.hire_date)
LIMIT 10;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 |
| 10002 | 1964-06-02 | Bezalel | Simmel | F | 1985-11-21 |
| 10003 | 1959-12-03 | Parto | Bamford | M | 1986-08-28 |
| 10004 | 1954-05-01 | Chirstian | Koblick | M | 1986-12-01 |
| 10005 | 1955-01-21 | Kyoichi | Maliniak | M | 1989-09-12 |
| 10006 | 1953-04-20 | Anneke | Preusig | F | 1989-06-02 |
| 10007 | 1957-05-23 | Tzvetan | Zielinski | F | 1989-02-10 |
| 10008 | 1958-02-19 | Saniya | Kalloufi | M | 1994-09-15 |
| 10009 | 1952-04-19 | Sumant | Peac | F | 1985-02-18 |
| 10010 | 1963-06-01 | Duangkaew | Piveteau | F | 1989-08-24 |
+--------+------------+------------+-----------+--------+------------+
10 rows in set (0.56 sec)
【関連記事】
▶SQLのwhere句でexistsを使ってサブクエリに合致するレコードを抽出
サブクエリを使ったview作成
サブクエリを使ってviewを作成することが可能です。以下は、サブクエリ内で２つのselect文をUNION ALLで１つに合成した結果を、viewとして作成した例です。
CREATE VIEW subquerytest
AS
SELECT *
FROM (SELECT emp_no,
first_name,
last_name,
hire_date
FROM employees
WHERE birth_date > '1970-01-01'
AND gender = 'F'
UNION ALL
SELECT emp_no,
first_name,
last_name,
hire_date
FROM employees
WHERE birth_date < '1960-01-01'
AND gender = 'M') t1
ORDER BY last_name,
emp_no;
mysql> select * from subquerytest limit 10;
+--------+------------+-----------+------------+
| emp_no | first_name | last_name | hire_date |
+--------+------------+-----------+------------+
| 12516 | Sreenivas | Aamodt | 1990-03-06 |
| 12982 | Sachem | Aamodt | 1992-01-11 |
| 17400 | Basim | Aamodt | 1991-09-15 |
| 18182 | Dekang | Aamodt | 1988-05-25 |
| 27188 | Vasilii | Aamodt | 1996-10-12 |
| 27413 | Phuoc | Aamodt | 1987-08-28 |
| 28387 | Nahla | Aamodt | 1997-02-15 |
| 29182 | Arumugam | Aamodt | 1986-01-09 |
| 31701 | Carrsten | Aamodt | 1985-10-07 |
| 35101 | Christ | Aamodt | 1990-02-05 |
+--------+------------+-----------+------------+
【関連記事】
▶SQLのview作成 joinやサブクエリを使ったselectも使用可能
ただし、サブクエリをキーを使わず結合するような「遅いクエリ」にすると、select文を発行するたびにデータベースに負荷がかかるので注意が必要です。
サブクエリで、重複データを削除するSQL
以下は、salaries（年収）テーブルから、emp_no（社員番号）が重複するデータを、to_dateが最新のものだけを残して削除する例です。
delete from salaries where (emp_no, to_date) not in ( select tmp1.emp_no as emp_no, tmp1.to_date as to_date from ( select emp_no, max(to_date) as to_date from salaries group by emp_no ) as tmp1 )
【関連記事】
▶SQLで重複を削除するサンプルコード 最新データを残してdeleteするには？
サブクエリ内でto_dateが最新のemp_noのみを抽出するクエリを実行し、delete文のwhere句でnot inを使って「それ以外」を削除対象に指定しています。
まとめ
- 副問合せを使って同一のテーブル内で結合を行う「自己結合」が可能
- 副問合せを使って、複数テーブルのデータを1テーブルに合成したviewを作成可能
- 副問合せを使って、重複データを１つ残して削除することが可能
