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
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
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
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 ...