MariaDB:select句のサブクエリの値をwhereで絞り込むSQLのサンプル

MariaDB:select句のサブクエリの値をwhereで絞り込むSQLのサンプル

select句のカラム指定する箇所にサブクエリを指定することができます。
そうすることで、検索するテーブルのカラムを基に一意に抽出できる別テーブルの値を検索結果に含めることができるようになります。

例として次のようなテーブルで考えてみましょう。

select * from company;
+----+-----------------------------+
| id | name                        |
+----+-----------------------------+
|  1 | ABCカンパニー               |
|  2 | XXX商事                     |
|  3 | あいうえお株式会社          |
+----+-----------------------------+
select * from employee;
+----+--------+------------+
| id | name   | company_id |
+----+--------+------------+
|  1 | 太郎   |          1 |
|  2 | 次郎   |          2 |
|  3 | 三郎   |          2 |
|  4 | 四郎   |          3 |
|  5 | 五郎   |          1 |
+----+--------+------------+

「company」テーブルが会社テーブルで、「employee」テーブルが社員テーブルとします。
社員テーブルの「company_id」が会社テーブル「id」の外部キーになっています。

このとき、
「社員名とその社員が属する会社名が分かるSQL」を作成するとしたら、いくつか方法がありますが、
その一つに以下のようなSQLが考えられます。

select name, (select name from company where employee.company_id=company.id) from employee;
+--------+-----------------------------------------------------------------+
| name   | (select name from company where employee.company_id=company.id) |
+--------+-----------------------------------------------------------------+
| 太郎   | ABCカンパニー                                                   |
| 次郎   | XXX商事                                                         |
| 三郎   | XXX商事                                                         |
| 四郎   | あいうえお株式会社                                              |
| 五郎   | ABCカンパニー                                                   |
+--------+-----------------------------------------------------------------+

select句にサブクエリを作成することで、別テーブル(company)の値を検索結果に含めているのです。

さて、このとき更に
「属する会社名に「XXX」が含まれる社員名とその会社名が分かるSQL」を作成する場合には、以下のようにwhere句にもサブクエリを入れる必要があります。
select句内のサブクエリに別名をつけたり、サブクエリ内のカラム(この場合はname)に別名を付けたりしても、where句で指定することができません。

select name, (select name from company where employee.company_id=company.id) from employee where (select name from company where employee.company_id=company.id) like '%XXX%';
+--------+-----------------------------------------------------------------+
| name   | (select name from company where employee.company_id=company.id) |
+--------+-----------------------------------------------------------------+
| 次郎   | XXX商事                                                         |
| 三郎   | XXX商事                                                         |
+--------+-----------------------------------------------------------------+

但しこうなるとselect句とwhere句で全く同じselectを書いていることになり、記述が冗長な感じがします。
別の書き方として、以下のようにサブクエリを二重に使う方法も考えられます。

select * from (select name, (select name from company where employee.company_id=company.id) n from employee) e where e.n like '%XXX%';
+--------+-----------+
| name   | n         |
+--------+-----------+
| 次郎   | XXX商事   |
| 三郎   | XXX商事   |
+--------+-----------+

こうすると、where句には外側のサブクエリのエイリアスである「e」の内側のサブクエリのエイリアスである「n」、という意味で「e.n」が使えるので最後のwhere句がシンプルに書けるようになります。

こういう場合はjoinを使って記述したり、where句でサブクエリを使う方が楽で分かりやすい感じがしますが、
select句でサブクエリを使った場合の例として挙げてみました。