O'Reilly logo

Beginning SQL by John W. Colby, Paul Wilton

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

Chapter 8. Queries within Queries

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, WHERE clauses, 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 WHERE or HAVING clause; to update and delete data by using subqueries with UPDATE and DELETE statements; and with operators such as EXISTS, ANY, SOME, and ALL, which are introduced later in this chapter.

Subqueries are particularly powerful when coupled with SQL operators such as IN, ANY, SOME, and 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.

Subquery Terminology

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

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