Subqueries
A subquery is a SELECT
statement nested within another SQL statement. This feature became
available as of version 4.1 of MySQL. Although the same results can be
accomplished by using the JOIN
clause or
UNION
, depending on the situation, subqueries are a
cleaner approach that is sometimes easier to read. They make a complex
query more modular, which makes it easier to create and to troubleshoot.
Here is a simple example of a subquery:
SELECT * FROM (SELECT col1, col2 FROM table1 WHERE col_id = 1000) AS derived1 ORDER BY col2;
In this example, the subquery or inner query
is a SELECT
statement specifying two column names. The other query is called the main or
outer query. It doesn’t have to be a
SELECT
. It can be an INSERT
, a
DELETE
, a DO
, an
UPDATE
, or even a SET
statement. The
outer query generally can’t select data or modify data from the same table
as an inner query, but this doesn’t apply if the subquery is part of a
FROM
clause. A subquery can return a value (a scalar),
a field, multiple fields containing values, or a full results set that
serves as a derived table.
You can encounter performance problems with subqueries if they are
not well constructed. One problem occurs when a subquery is placed within
an IN()
clause as part of a
WHERE
clause. It’s generally better to use the
=
operator for each value, along with
AND
for each parameter/value pair.
When you see a performance problem with a subquery, try
reconstructing the SQL statement with JOIN
and compare the differences ...
Get MySQL in a Nutshell, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.