This chapter examines how you can nest one query inside another. SQL allows queries within queries, or subqueries, which are
SELECT statements inside
SELECT statements. This might sound a bit odd, but subqueries can actually be very useful. The downside, however, is that they can consume a lot of processing, disk, and memory resources.
A subquery's syntax is just the same as a normal
SELECT query's syntax. As with a normal
SELECT statement, a subquery can contain joins,
HAVING clauses, and
GROUP BY clauses. Specifically, this chapter shows you how to use subqueries with
SELECT statements, either returning results inside a column list or helping filter results when used inside a
HAVING clause; to update and delete data by using subqueries with
DELETE statements; and with operators such as
ALL, which are introduced later in this chapter.
Subqueries are particularly powerful when coupled with SQL operators such as
ALL, which are covered shortly. However, the chapter begins with a few easy examples of subqueries. Before starting, you should note that versions of MySQL prior to version 4.1 do not fully support subqueries and many of the examples in this chapter won't work on early versions of MySQL.
Throughout this chapter, you'll notice references to the outer and inner subqueries. The outer query is the main
SELECT statement, and you could say that so far ...