O'Reilly logo

MySQL in a Nutshell, 2nd Edition by Russell J.T. Dyer

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required