mysql에서의 subquery

아래는 원본입니다.
http://dev.mysql.com/doc/mysql/en/Subqueries.html

subquery 는 mysql 4.1 부터 적용가능합니다. 고로 지금은 힘들더라도 아쉽지만 노가다를 해야겠네요..ㅠ.ㅠ




A subquery is a SELECT statement inside another statement.

Starting with MySQL 4.1, all subquery forms and operations that the SQL standard requires are supported, as well as a few features that are MySQL-specific.

With earlier MySQL versions, it was necessary to work around or avoid the use of subqueries, but people starting to write code now will find that subqueries are a very useful part of the MySQL toolkit.

For MySQL versions prior to 4.1, most subqueries can be successfully rewritten using joins and other methods. See section 14.1.8.11 Rewriting Subqueries as Joins for Earlier MySQL Versions.

Here is an example of a subquery:

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

In this example, SELECT * FROM t1 ... is the outer query (or outer statement), and (SELECT column1 FROM t2) is the subquery. We say that the subquery is nested in the outer query, and in fact it's possible to nest subqueries within other subqueries, to a great depth. A subquery must always appear within parentheses.

The main advantages of subqueries are:

They allow queries that are structured so that it's possible to isolate each part of a statement.
They provide alternative ways to perform operations that would otherwise require complex joins and unions.
They are, in many people's opinion, readable. Indeed, it was the innovation of subqueries that gave people the original idea of calling the early SQL ``Structured Query Language.''
Here is an example statement that shows the major points about subquery syntax as specified by the SQL standard and supported in 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)));

Currently, you cannot modify a table and select from the same table in a subquery. This applies to statements such as DELETE, INSERT, REPLACE, and UPDATE.<div class='small'>[이 게시물은 관리자님에 의해 2011-10-31 17:27:00 MySQL에서 이동 됨]</div>