Subqueries and Query Expressions
(Page 1 of 12 )
The SQL subquery feature makes it easier to express certain types of queries in SQL; indeed, some queries can't be expressed at all without using a subquery. Keep reading to learn more abou this useful feature. This article is excerpted from chapter 9 of
MySQL The Complete Reference, written by Vikram Vaswani and published by McGraw Hill.
The SQL subquery feature lets you use the results of one query as part of another query. The ability to use a query within a query was the original reason for the word “structured” in the name Structured Query Language. The subquery feature is less well known than SQL’s join feature, but it plays an important role in SQL for three reasons:
- A SQL statement with a subquery is often the most natural way to express a query, because it most closely parallels the English-language description of the query.
- Subqueries make it easier to write SELECT statements, because they let you break a query down into pieces (the query and its subqueries) and then put the pieces back together.
- Some queries cannot be expressed in the SQL language without using a subquery.
The first several sections of this chapter describe subqueries and show how they are used in the WHERE and HAVING clauses of a SQL statement. The later sections of this chapter describe the advanced query expression capabilities that have been added to the SQL2 standard, which substantially expands the power of SQL to perform even the most complex of database operations.
Using Subqueries A subquery is a query within a query. The results of the subquery are used by the DBMS to determine the results of the higher-level query that contains the subquery. In the simplest forms of a subquery, the subquery appears within the WHERE or HAVING clause of another SQL statement. Subqueries provide an efficient, natural way to handle query requests that are themselves expressed in terms of the results of other queries. Here is an example of such a request:
List the offices where the sales target for the office exceeds the sum of the individual salespeople’s quotas.
The request asks for a list of offices from the OFFICES table, where the value of the TARGET column meets some condition. It seems reasonable that the SELECT statement that expresses the query should look something like this:
SELECT CITY FROM OFFICES WHERE TARGET > ???
The value “???” needs to be filled in and should be equal to the sum of the quotas of the salespeople assigned to the office in question. How can you specify that value in the query? From Chapter 8, you know that the sum of the quotas for a specific office (say, office number 21) can be obtained with this query:
SELECT SUM (QUOTA)
FROM SALESREPS
WHERE REP_OFFICE = 21
But it would be inefficient to have to type in this query, write down the results, and then type in the previous query with the correct amount. How can you put the results of this query into the earlier query in place of the question marks? It would seem reasonable to start with the first query and replace the “???” with the second query, as follows:
SELECT CITY
FROM OFFICES
WHERE TARGET > (SELECT SUM(QUOTA)
FROM SALESREPS
WHERE REP_OFFICE = OFFICE)
In fact, this is a correctly formed SQL query. For each office, the inner query (the subquery) calculates the sum of the quotas for the salespeople working in that office. The outer query (the main query) compares the office’s target to the calculated total and decides whether to add the office to the main query results. Working together, the main query and the subquery express the original request and retrieve the requested data from the database.
SQL subqueries typically appear as part of the WHERE clause or the HAVING clause. In the WHERE clause, they help to select the individual rows that appear in the query results. In the HAVING clause, they help to select the row groups that appear in the query results.
 This article is excerpted from MySQL The Complete Reference by Vikram Vaswani (McGraw-Hill, 2003; ISBN 0072224770). Check it out at your favorite bookstore today. Buy this book now. |
Next: What Is a Subquery? >>
More MS SQL Server Articles
More By McGraw-Hill/Osborne
/ 21 
