13.2.10 サブクエリー構文

サブクエリーは、別のステートメント内の SELECT ステートメントです。

MySQL 4.1 から、SQL 標準に必要なサブクエリーのすべての形式および操作だけでなく、MySQL 固有のいくつかの機能がサポートされています。

サブクエリーの例を次に示します。

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

この例では、SELECT * FROM t1 ...外部クエリー (または 外部ステートメント) であり、(SELECT column1 FROM t2)サブクエリーです。これを、このサブクエリーは外部クエリー内でネストされていると表現し、また実際、サブクエリーをほかのサブクエリー内で (かなりの深さまで) ネストできます。サブクエリーは常に、括弧内に指定する必要があります。

サブクエリーの主な利点は次のとおりです。

  • ステートメントの各部分を分離できるように、構造化されたクエリーを可能にします。

  • 通常であれば複雑な結合や和集合を必要とする操作を実行するための代替手段を提供します。

  • 多くの人びとが、サブクエリーを複雑な結合や和集合より読みやすいと感じています。実際、早期の SQL である構造化クエリー言語を呼び出すという元の考え方を人びとに提供したのは、サブクエリーの技術革新でした。

SQL 標準で指定され、MySQL でサポートされているサブクエリー構文に関する主なポイントを示すステートメントの例を次に示します。

DELETE FROM t1
WHERE s11 > ANY
 (SELECT COUNT(*) /* no hint */ FROM t2
  WHERE NOT EXISTS
   (SELECT * FROM t3
    WHERE ROW(5*t2.s1,77)=
     (SELECT 50,11*s1 FROM t4 UNION SELECT 50,77 FROM
      (SELECT * FROM t5) AS t5)));

サブクエリーは、スカラー (単一値)、単一行、単一カラム、またはテーブル (1 つ以上のカラムの 1 つ以上の行) を返すことができます。これらは、スカラー、カラム、行、およびテーブルサブクエリーと呼ばれます。特定の種類の結果を返すサブクエリーは多くの場合、次の各セクションで説明されているように、特定のコンテキストでのみ使用できます。

サブクエリーを使用できるステートメントのタイプに関する制限はほとんどありません。サブクエリーには、DISTINCTGROUP BYORDER BYLIMIT、結合、インデックスヒント、UNION 構造構文、コメント、関数などの、通常の SELECT に含めることのできる多くのキーワードや句を含めることができます。

サブクエリーの外部ステートメントは、SELECTINSERTUPDATEDELETESETDO のいずれでもかまいません。

MySQL では、テーブルを変更し、さらにサブクエリーで同じテーブルから選択することはできません。これは、DELETEINSERTREPLACEUPDATELOAD DATA INFILE (サブクエリーは SET 句で使用できるため) などのステートメントに適用されます。

オプティマイザによるサブクエリーの処理方法については、セクション8.2.1.18「サブクエリーの最適化」を参照してください。サブクエリーの使用に関する制限の説明 (特定の形式のサブクエリー構文でのパフォーマンスの問題を含む) については、セクションD.4「サブクエリーの制約」を参照してください。


User Comments
  Posted by Are you mortal Then prepare to die. on January 5, 2005

Ever wanted to turn an AUTO_INCRIMENT primary key into one of those 'rolling ID' columns? i.e. the type which changes back to ID = 1 when some other part of your (new) PK changes... Use a subquery!

Suppose you have this

TABLE t1...

AUTO_INCR_PK <-> X
1 <-> A
2 <-> A
3 <-> A
4 <-> B
5 <-> B
6 <-> B
7 <-> C
8 <-> C
9 <-> D

Try this cool sub-query!

CREATE TABLE t2 ( ID, X, PK(ID,X) );

INSERT INTO t2;
SELECT
a.X
a.AUTO_INCR_PK -
b.FIRST_KEY_IN_SERIES AS ID
FROM
t1
INNER JOIN
(
SELECT
X,
MIN(AUTO_INCR_PK) AS FIRST_KEY_IN_SERIES
FROM
t1
GROUP BY
X
) AS b
USING
(X)
;

Which gives you

TABLE t2 ...

ID <-> X
1 <-> A
2 <-> A
3 <-> A
1 <-> B
2 <-> B
3 <-> B
1 <-> C
2 <-> C
1 <-> D

Cool eh?

  Posted by Tayfun Duran on September 10, 2005
If you can't use subquery, you can use this;

$sec1 = mysql_query("SELECT foto FROM profoto WHERE proje=$id");
if ($kyt1 = mysql_fetch_array($sec1)) {$dizi = $kyt1["foto"];} else {$dizi="0";}
while ($kyt1 = mysql_fetch_array($sec1)) {
$dizi = $dizi . "," . $kyt1["foto"];
}
mysql_free_result($sec1);
$sec = mysql_query("SELECT foto.id, foto.dosya, foto.baslik FROM foto WHERE id NOT IN (" . $dizi . ") LIMIT $baslangic, $sayfalama");

First, you create a selection and then you use it in your real selection. This is a kind of subquery :)

  Posted by Svavar Lúthersson on June 4, 2006
This order of things also works for count functions. For example:

SELECT *,(SELECT COUNT(*) FROM table2 WHERE table2.field1 = table1.id) AS count FROM table1 WHERE table1.field1 = 'value'

This command will enable you to count fields in table2 based on a column value in table1 and label the result as "count". The value in table1.field1 can be any valid field type.
  Posted by ch4dwick m. on May 21, 2007
Here's simple insert query I came up with that serves 3 functions in my PHP applications:
1.) retrieves data from the select query
2.) Inserts that row into another table (for record keeping purposes such as purchase orders from shopping carts)
3.) validates the input (using mysql_affected_rows()) and will return 0 if the select failed and will not insert at all.

INSERT INTO table2 (field1, field2, field3, field4) (SELECT 'value1 from user input', field1, field2, field3 from table1)

The 4 fields in table2 will be populated by the 4 fields (including the string) returned by the SELECT sub-query respectively.

I know this MIGHT raise issues with speed of queries but it's better than writing long lines of PHP code that does those 3 things - even with a framework! I can just use the mysql_affected_rows() after that query to see if everything went fine.

NOTE: Make sure the number of fields in the SELECT query is EXACTLY the same number of fields you are about to insert.
  Posted by Andy Leon on June 23, 2009
It mentions here that you cannot select from and modify the same table as part of your subquery. There is a workaround listed on the following page:

http://dev.mysql.com/doc/refman/5.1/en/subquery-restrictions.html

that shows you how to use a temporary table. But you can also create a View based on a table, use that for the SELECT statement and then use the regular table name for the UPDATE / DELETE statement.
  Posted by Christian Berrigan on March 14, 2010
Updating a table using a subquery. This uses an example of a table of people, and a separate table of votes those people have received. After the votes table has been populated with new votes, the total vote count of each of the people is calculated and updated with one query.

UPDATE people,
(SELECT count(*) as votecount, person_id
FROM votes GROUP BY person_id) as tally
SET people.votecount = tally.votecount
WHERE people.person_id = tally.person_id

  Posted by Devang Modi on August 30, 2011
Combine queries for Insert and Select always obeys Innodb locking rules
if one of the source table is based on Innodb engine.
It is also possible that the INSERT activity applicable to TEMPORARY
table which is not InnoDB engine. It is also possible that in SELECT
section with INNODB, some other TEMPORARY Tables are used.
Devang Modi
Sign Up Login You must be logged in to post a comment.