Skip to Main Content
MySQL in a Nutshell, 2nd Edition
book

MySQL in a Nutshell, 2nd Edition

by Russell J. T. Dyer
April 2008
Intermediate to advanced content levelIntermediate to advanced
566 pages
21h 55m
English
O'Reilly Media, Inc.
Content preview from MySQL in a Nutshell, 2nd Edition

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

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

MySQL in a Nutshell

MySQL in a Nutshell

Russell J. T. Dyer
MySQL Reference Manual

MySQL Reference Manual

Michael Widenius, David Axmark, Kaj Arno
MySQL Cookbook

MySQL Cookbook

Paul DuBois

Publisher Resources

ISBN: 9780596514334Errata Page