この記事は、13年前に書かれました。
はじめに
サブクエリとは、クエリ内にさらにクエリを入れ子にしたものです。どういった場面で使用するかは様々ですが、私が今回実際の案件で使用した方法をご紹介します。
動作確認環境
- MySQL5.3.x
親子関係のあるテーブルから親のリストと子の件数を取得したい
例えば以下のようなpersonsテーブルがあったとします。
少々特殊な事例ですが、parentの値に親のIDが入っているレコードが子となります。必然的に”0”は親レコードとなります。
例えば「1：Sato Takeshi」は親となり、IDが2，3のレコードは「1：Sato Takeshi」の子となります。
事情がありこのような構造となっていますが、このテーブルから親のみのリストと、その親にぶらさがる子の件数を１つのSQL文にて一括取得してみたいと思います。
id | name | parent |
----+-----------------+---------+
1 | Sato Takeshi | 0 |
2 | Sato Yukari | 1 |
3 | Sato Nami | 1 |
4 | Yamada Takayuki | 0 |
5 | Yamada Taro | 4 |
6 | Yamada Shinji | 4 |
7 | Tanaka Keisuke | 0 |
8 | Tanaka Akemi | 8 |
(8 rows)
まず、それぞれの親に属する子の人数をカウントするSQLを単独で作成してみます。
SELECT parent AS parent_id , count(parent) AS child_count FROM parents GROUP BY parent;問い合わせ結果は以下のとおりです。
parent_id | child_count|
-----------+------------+
0 | 3 |
1 | 2 |
4 | 2 |
7 | 1 |
(4 rows)count()はSQLのカウント関数ですが、AS句を利用してカウント結果をchild_countという別名にしています。また「parent AS parent_id」と別名指定し、paretをparent_idに置き換えています。これは、あとでサブクエリを組み立てるときに使用するためです。
サブクエリ化の完成
子のカウントが取得できたところで、いよいよサブクエリの出番です。
以下のようなSQLとなります。
SELECT m.id, m.name, s.child_count FROM parents AS m
LEFT JOIN ( SELECT parent AS parent_id , count(parent) AS child_count FROM parents GROUP BY parent )
AS s ON m.id = s.parent_id
WHERE m.parent = 0 ORDER BY m.name;一行目のSELECT～は最終結果を出力するSQLとなりますので後ほど説明します。
２行目のLEFT JOIN～に続く( )内のSELECT文がサブクエリとなります。このSQL文は、先ほど子のカウントを取得したSQL文です。
LEFT JOINは左外部結合という機能です。１行目の「FROM parents AS m」と別名指定しているmテーブルと結合しており、左のテーブルはすべて表示するという意味になります。他にも内部結合、外部結合などがありますが、ここでは説明を割愛します。
３行目の「AS s」以降が重要で、サブクエリの問い合わせ結果をテーブル名”ｓ”として別名にしています。さらに「ON m.id = s.parent_id」という箇所で、左のテーブル(parent AS m)と右のテーブル（サブクエリ結果 s）のidが一致するものを結合条件としています。
WHERE句では、「m.paret = 0」を指定していますが、parentが０のレコードは必然的に親レコードとなりますので、親のみを抽出するという意味になります。
最終的に、１行目にて「m.id, m.name, s.child_count」を表示していますが、テーブルを別名指定していますので、mかsどちらのカラムを表示させるかを明示的に指定します。
問い合わせ結果は結果は以下のようになります。
id | name | child_count|
-----------------------+------------+
1 | Yamada Takayuki | 2 |
4 | Tanaka Keisuke | 1 |
7 | Sato Takeshi | 2 |
(3 rows)サブクエリは奥が深く、理解するのは少々難しいのですが、非常に便利な機能です。
実際の案件では、製品テーブルにて以下のようにサブクエリを使用しました。
select m.products_id,m.name1,m.viewno,s.child_count from products AS m
left join ( select parent AS child_id , count(parent) as child_count from products group by parent ) AS s
on m.products_id = s.child_id
where category_id= 2 AND m.parent = 0 AND m.viewflg = 0 ORDER BY m.viewnoまとめ
SQL文のSELECT文を単体で使用する場合は非常に単純です。
しかし、あるテーブルの検索結果によって別のテーブルの検索をおこなうという複雑な問い合わせでは、サブクエリが必須となります。INNER JOIN, LEFT JOIN , RIGHT JOINといったものも頻繁に使用することになりますので、これらについても理解を深める必要があります。
今回は１つのテーブルでサブクエリを使用する特殊な例でしたが、考え方は特に変わりありません。