サブクエリを使った検索条件の設定

サブクエリとは SELECT 文で取得した結果を他の SELECT 文や UPDATE 文の中で利用する使い方です。主に WHERE 句の中で使用されることが多いのですがそれには限定されません。サブクエリを利用できる文は SELECT 文、 INSERT 文、 UPDATE 文、 DELETE 文、 SET 文、 DO 文のいずれかだけです。ここでは MySQL でサブクエリを使った検索条件の設定方法について解説します。

(Last modified: 2023年01月07日)

サブクエリで取得した1つの値と比較する
サブクエリで取得した複数の値と比較する(ANY)
サブクエリで取得した複数の値と比較する(ALL)

サブクエリで取得した1つの値と比較する

最初に WHERE 句の条件式でサブクエリで取得した 1 つの値と比較する使い方です。次のように使います。

SELECT col_name1 FROM table_name
  WHERE col_name1 = (SELECT col_name2 FROM table_name2 WHERE ...)

サブクエリのSELECT文は全体を括弧で囲んで記述します。

SELECT 文で WHERE 句を使った条件式を記述する場合、カラムの値が例えば 5 と等しいデータを取得するといった使い方をしますが、サブクエリを使うとカラムの値と比較する 5 という数値の代わりに別の SELECT 文で取得した値と比較することができます。

ただ 1 つの値と比較する必要があるので、サブクエリの SELECT 文では取得するデータは 1 つのカラムの値だけで、データの数も 1 つである必要があります。

-- --

それでは実際に試してみます。次のようなテーブルを作成し、テーブルにはデータを追加してあります。製品に関するテーブルです。

create table product (id int, name varchar(10));

insert into product values (1, 'Desk');
insert into product values (2, 'Chair');
insert into product values (3, 'Clock');

サブクエリで取得した値を検索条件で使用する(1)

もう一つ在庫に関するテーブルを作成します。テーブルにはデータが追加してあります。

create table stock (productid int, stockcount int);

insert into stock values (1, 10);
insert into stock values (2, 8);
insert into stock values (3, 12);

サブクエリで取得した値を検索条件で使用する(2)

それでは在庫の数が一番多い製品名を取得してみます。最初に stock テーブルから一番在庫が多い製品の productid を取得します。次のように実行してください。

select productid from stock order by stockcount desc limit 1;

サブクエリで取得した値を検索条件で使用する(3)

この SELECT 文をサブクエリとして利用し次のように実行してください。

select * from product where id = (select productid from stock order by stockcount desc limit 1);

サブクエリで取得した値を検索条件で使用する(4)

サブクエリを使って在庫が一番多い productid を取得し、 product テーブルから id カラムの値がサブクエリで取得した productid と一致するデータを取得しました。

サブクエリで取得した複数の値と比較する(ANY)

先ほどはサブクエリでただ一つの値を取得し、条件式の中で比較の対象として使用しました。今度はサブクエリで複数の値を取得し、その中のいずれか一つでも条件式を満たすかどうかを調べる使い方です。次のように ANY を記述します。

SELECT col_name1 FROM table_name
  WHERE col_name1 = ANY (SELECT col_name2 FROM table_name2 WHERE ...)

※ ANY の別名として SOME と書いても同じです。

サブクエリの SELECT 文では取得するデータは 1 つのカラムの値だけですが、データの数は複数取得することができます。

-- --

それでは実際に試してみます。次のようなテーブルを作成し、テーブルにはデータを追加してあります。生徒に関するテーブルです。

create table student (id int, name varchar(10));

insert into student values (1, 'Yamda');
insert into student values (2, 'Suzuki');
insert into student values (3, 'Honda');
insert into student values (4, 'Mimura');

サブクエリで取得した複数の値と比較する(ANY)(1)

もう一つテスト結果に関するテーブルを作成します。テーブルにはデータが追加してあります。

create table test (studentid int, result int);

insert into test values (1, 84);
insert into test values (2, 68);
insert into test values (3, 73);
insert into test values (4, 92);

サブクエリで取得した複数の値と比較する(ANY)(2)

それではテストの結果が80点以上の生徒を取得してみます。最初に test テーブルから result カラムの値が 80 以上の studentid を取得します。次のように実行してください。

select studentid from test where result >= 80;

サブクエリで取得した複数の値と比較する(ANY)(3)

この SELECT 文をサブクエリとして利用し次のように実行してください。

select * from student where id = any (select studentid from test where result >= 80);

サブクエリで取得した複数の値と比較する(ANY)(4)

サブクエリを使ってテストの結果が80点以上の studentid を取得し、 student テーブルから id カラムの値がサブクエリで取得した studentid カラムのいずれかの値と一致するデータを取得しました。

サブクエリで取得した複数の値と比較する(ALL)

サブクエリで複数の値を取得し、その中の全ての値に対して条件式を満たすかどうかを調べる使い方です。次のように ALL を記述します。

SELECT col_name1 FROM table_name
  WHERE col_name1 = ALL (SELECT col_name2 FROM table_name2 WHERE ...)

サブクエリの SELECT 文では取得するデータは 1 つのカラムの値だけですが、データの数は複数取得することができます。

-- --

それでは実際に試してみます。次のようなテーブルを作成し、テーブルにはデータを追加してあります。製品に関するテーブルです。

create table product (id int, name varchar(10));

insert into product values (1, 'Desk');
insert into product values (2, 'Chair');
insert into product values (3, 'Clock');
insert into product values (4, 'Light');
insert into product values (5, 'Sofa');

サブクエリで取得した複数の値と比較する(ALL)(1)

もう一つ在庫に関するテーブルを作成します。テーブルにはデータが追加してあります。

create table stock (productid int, stockcount int);

insert into stock values (1, 14);
insert into stock values (2, 8);
insert into stock values (3, 16);
insert into stock values (4, 7);
insert into stock values (5, 9);

サブクエリで取得した複数の値と比較する(ALL)(2)