SQL Run

Database Programming

« Previous

Home

Contents

» Next

8. Subqueries

To this point, I’ve used a single SELECT statement to retrieve data from one or more tables. This chapter describes nested queries, which let you retrieve or modify data based on another query’s result.

A subquery, or subselect, is a SELECT statement embedded in another SQL statement. You can nest a subquery in:

• The SELECT, FROM, WHERE, or HAVING clause of a SELECT statement
• Another subquery
• An INSERT, UPDATE, or DELETE statement

In general, you can use a subquery anywhere an expression is allowed, but your DBMS might restrict where they can appear. This chapter covers subqueries nested in a SELECT statement or another subquery; Chapter 10 covers subqueries embedded in INSERT, UPDATE, and DELETE statements.

Understanding Subqueries

This section defines some terms and introduces subqueries by giving an example of a SELECT statement that contains a simple subquery. Subsequent sections explain the types of subqueries and their syntax and semantics.

Suppose that you want to list the names of the publishers of biographies. The naive approach is to write two queries: one query to retrieve the IDs of all the biography publishers (Listing 8.1 and Figure 8.1) and a second query that uses the first query’s result to list the publisher names (Listing 8.2 and Figure 8.2).

A better way is to use an inner join (Listing 8.3 and Figure 8.3); see “Creating an Inner Join with INNER JOIN” in Chapter 7.

Another alternative is to use a subquery (Listing 8.4 and Figure 8.4). The subquery in Listing 8.4 is highlighted. A subquery also is called an inner query, and the statement containing a subquery is called an outer query. In other words, an enclosed subquery is an inner query of an outer query. Remember that a subquery can be nested in another subquery, so inner and outer are relative terms in statements with multiple nested subqueries.

I’ll explain how a DBMS executes subqueries in “Simple and Correlated Subqueries” later in this chapter, but for now, all that you need to know is that in Listing 8.4, the DBMS processes the inner query (highlighted text) first and then uses its interim result to run the outer query (plain text) and get the final result. The IN keyword that introduces the subquery tests for list membership and works like IN in “List Filtering with IN” in Chapter 4. Note that the inner query in Listing 8.4 is the same query as Listing 8.1, and the outer query is the same query as Listing 8.2.

Tips for Subqueries

• Sometimes you’ll see the term subquery used to refer to an entire SQL statement that contains one or more subqueries. To prevent confusion, I don’t use that terminology in this book.

• MySQL 4.1 and later support subqueries, but earlier versions don’t. You can’t run the examples in this chapter if you’re using MySQL 4.0 or earlier, but you have a few choices, in order of preference:

  • Upgrade to the latest version of MySQL (mysql.com).
  • Recast the subquery as a join (see “Subqueries vs. Joins” later in this chapter).
  • Create a temporary table to hold the result of a subquery (see “Creating a Temporary Table with CREATE TEMPORARY TABLE” in Chapter 11 and the temporary-table example in the DBMS tip in “Tips for Listing 7.32” in Chapter 7).
  • Simulate the subquery in a procedural host language such as PHP or Java.

Subquery Syntax

The syntax of a subquery is the same as that of a normal SELECT statement (see Chapters 4 through 7) except for the following differences:

• You can nest a subquery in a SELECT, FROM, WHERE, or HAVING clause or in another subquery.
• Always enclose a subquery in parentheses.
• Don’t terminate a subquery with a semicolon. (You still must terminate the statement that contains the subquery with a semicolon.)
• Don’t put an ORDER BY clause in a subquery. (A subquery returns an intermediate result that you never see, so sorting a subquery makes no sense.)
• A subquery is a single SELECT statement. (You can’t use, say, a UNION of multiple SELECT statements as a subquery.)
• A subquery can use columns in the tables listed in its own FROM clause or in the outer query’s FROM clause.
• If a table appears in an inner query but not in the outer query, then you can’t include that table’s columns in the final result (that is, in the outer query’s SELECT clause).

• Depending on the context in which it’s used, a subquery might be required to return a limited number of rows or columns. The SQL standard categorizes a subquery by the number of rows and columns it returns (Table 8.1). In all cases, the subquery also can return an empty table (zero rows).

  Table 8.1Size of Subquery Results

  Subquery	Rows	Columns
  Scalar subquery	1	1
  Row subquery	1	≥1
  Table subquery	≥1	≥1

In practice, a subquery usually appears in a WHERE clause that takes one of these forms:

• WHERE test_expr op (subquery)
• WHERE test_expr [NOT] IN (subquery)
• WHERE test_expr op ALL (subquery)
• WHERE test_expr op ANY (subquery)
• WHERE [NOT] EXISTS (subquery)

test_expr is a literal value, a column name, an expression, or a scalar subquery; op is a comparison operator (=, <>, <, <=, >, or >=); and subquery is a simple or correlated subquery. I’ll cover each of these forms later in this chapter. You can use these subquery forms in a HAVING clause, too.

Tips for Subquery Syntax

• The SQL standard doesn’t specify a maximum number of subquery nesting levels, so your DBMS will set its own upper limit. This built-in limit typically exceeds the limit of human comprehension. Microsoft SQL Server, for example, allows 32 levels of nesting.

Subqueries vs. Joins

In “Understanding Subqueries” earlier in this chapter, Listings 8.3 and 8.4 showed two equivalent queries: one used a join, and the other used a subquery. Many subqueries can be formulated alternatively as joins. In fact, a subquery is a way to relate one table to another without actually doing a join.

Because subqueries can be hard to use and debug, you might prefer to use joins, but you can pose some questions only as subqueries. In cases where you can use subqueries and joins interchangeably, you should test queries on your DBMS to see whether a performance difference exists between a statement that uses a subquery and a semantically equivalent statement that uses a join. For example, the query

SELECT MAX(table1.col1)
  FROM table1
  WHERE table1.col1 IN
    (SELECT table2.col1
       FROM table2);

usually will run faster than

SELECT MAX(table1.col1)
  FROM table1
  INNER JOIN table2
    ON table1.col1 = table2.col1;

For more information, see “Comparing Equivalent Queries” later in this chapter.

The following syntax diagrams show some equivalent statements that use subqueries and joins. These two statements are equivalent (IN subquery):

SELECT *
  FROM table1
  WHERE id IN
    (SELECT id FROM table2);

and (inner join):

SELECT DISTINCT table1.*
  FROM table1
  INNER JOIN table2
    ON table1.id = table2.id;

See Listings 8.5a and 8.5b and Figure 8.5 for an example.

These three statements are equivalent (NOT IN subquery):

SELECT *
  FROM table1
  WHERE id NOT IN
    (SELECT id FROM table2);

and (NOT EXISTS subquery):

SELECT *
  FROM table1
  WHERE NOT EXISTS
    (SELECT *
       FROM table2
       WHERE table1.id = table2.id);

and (left outer join):

SELECT table1.*
  FROM table1
  LEFT OUTER JOIN table2
    ON table1.id = table2.id
  WHERE table2.id IS NULL;

See Listings 8.6a, 8.6b, and 8.6c and Figure 8.6 for an example. IN and EXISTS subqueries are covered later in this chapter.

You also can write a self-join as a subquery (Listings 8.7a and 8.7b and Figure 8.7). For information about self-joins, see “Creating a Self-Join” in Chapter 7.

Favor subqueries if you’re comparing an aggregate value to other values (Listing 8.8 and Figure 8.8). Without a subquery, you’d need two SELECT statements to list all the books with the highest price: one query to find the highest price and a second query to list all the books selling for that price. For information about aggregate functions, see Chapter 6.

Use joins when you include columns from multiple tables in the result. Listing 8.5b uses a join to retrieve authors who live in the same city in which a publisher is located. To include the publisher ID in the result, simply add the column pub_id to the SELECT-clause list (Listing 8.9 and Figure 8.9).

You can’t accomplish this same task with a subquery because it’s illegal to include a column in the outer query’s SELECT-clause list from a table that appears in only the inner query:

SELECT a.au_id, a.city, p.pub_id
  FROM authors a
  WHERE a.city IN
    (SELECT p.city
       FROM publishers p);     --Illegal

Tips for Subqueries vs. Joins

• You always can express an inner join as a subquery, but not vice versa. This asymmetry occurs because inner joins are commutative; you can join tables A to B in either order and get the same answer. Subqueries lack this property. (You always can express an outer join as a subquery, too, even though outer joins aren’t commutative.)
• MySQL 4.0 and earlier don’t support subqueries; see the DBMS tip in “Tips for Subqueries” earlier in this chapter.

Simple and Correlated Subqueries

You can use two types of subqueries:

• Simple subqueries
• Correlated subqueries

A simple subquery, or noncorrelated subquery, is a subquery that can be evaluated independently of its outer query and is processed only once for the entire statement. All the subqueries in this chapter’s examples so far have been simple subqueries (except Listing 8.6b).

A correlated subquery can’t be evaluated independently of its outer query; it’s an inner query that depends on data from the outer query. A correlated subquery is used if a statement needs to process a table in the inner query for each row in the outer query.

Correlated subqueries have more-complicated syntax and a knottier execution sequence than simple subqueries, but you can use them to solve problems that you can’t solve with simple subqueries or joins. This section gives an example of a simple subquery and a correlated subquery and then describes how a DBMS executes each one. Subsequent sections in this chapter contain more examples of each type of subquery.

Simple Subqueries

A DBMS evaluates a simple subquery by evaluating the inner query once and substituting its result into the outer query. A simple subquery executes prior to, and independent of, its outer query.

Let’s revisit Listing 8.5a from earlier in this chapter. Listing 8.10 (which is identical to Listing 8.5a) uses a simple subquery to list the authors who live in the same city in which a publisher is located; see Figure 8.10 for the result. Conceptually, a DBMS processes this query in two steps as two separate SELECT statements:

1. The inner query (a simple subquery) returns the cities of all the publishers (Listing 8.11 and Figure 8.11).
2. The DBMS substitutes the values returned by the inner query in step 1 into the outer query, which finds the author IDs corresponding to the publishers’ cities (Listing 8.12 and Figure 8.12).

Correlated Subqueries

Correlated subqueries offer a more powerful data-retrieval mechanism than simple subqueries do. A correlated subquery’s important characteristics are:

• It differs from a simple query in its order of execution and in the number of times that it’s executed.
• It can’t be executed independently of its outer query because it depends on the outer query for its values.
• It’s executed repeatedly—once for each candidate row selected by the outer query.
• It always refers to the table mentioned in the FROM clause of the outer query.
• It uses qualified column names to refer to values specified in the outer query. In the context of correlated subqueries, these qualified named are called correlation variables. For information about qualified names and table aliases, see “Qualifying Column Names” and “Creating Table Aliases with AS” in Chapter 7.

• The basic syntax of a query that contains a correlated subquery is:

  SELECT outer_columns
  FROM outer_table
  WHERE outer_column_value IN
    (SELECT inner_column
       FROM inner_table
       WHERE inner_column = outer_column)

  Execution always starts with the outer query (plain text). The outer query selects each individual row of outer_table as a candidate row. For each candidate row, the DBMS executes the correlated inner query (highlighted text) once and flags the inner_table rows that satisfy the inner WHERE condition for the value outer_column_value. The DBMS tests the outer WHERE condition against the flagged inner_table rows and displays the flagged rows that satisfy this condition. This process continues until all the candidate rows have been processed.

Listing 8.13 uses a correlated subquery to list the books that have sales better than the average sales of books of its type; see Figure 8.13 for the result. candidate (following titles in the outer query) and average (following titles in the inner query) are alias table names for the table titles, so that the information can be evaluated as though it comes from two different tables (see “Creating a Self-Join” in Chapter 7).

In Listing 8.13, the subquery can’t be resolved independently of the outer query. It needs a value for candidate.type, but this value is a correlation variable that changes as the DBMS examines different rows in the table candidate. The column average.type is said to correlate with candidate.type in the outer query. The average sales for a book type are calculated in the subquery by using the type of each book from the table in the outer query (candidate). The subquery computes the average sales for this type and then compares it with a row in the table candidate. If the sales in the table candidate are greater than or equal to average sales for the type, then that book is displayed in the result. A DBMS processes this query as follows:

1. The book type in the first row of candidate is used in the subquery to compute average sales.

   Take the row for book T01, whose type is history, so the value in the column type in the first row of the table candidate is history. In effect, the subquery becomes:

   SELECT AVG(sales)
  FROM titles average
  WHERE average.type = 'history';

   This pass through the subquery yields a value of 6866—the average sales of history books. In the outer query, book T01’s sales of 566 are compared to the average sales of history books. T01’s sales are lower than average, so T01 isn’t displayed in the result.

2. Next, book T02’s row in candidate is evaluated.

   T02 also is a history book, so the evaluated subquery is the same as in step 1:

   SELECT AVG(sales)
  FROM titles average
  WHERE average.type = 'history';

   This pass through the subquery again yields 6866 for the average sales of history books. Book T02’s sales of 9566 are higher than average, so T02 is displayed in the result.

3. Next, book T03’s row in candidate is evaluated.

   T03 is a computer book, so this time, the evaluated subquery is:

   SELECT AVG(sales)
  FROM titles average
  WHERE average.type = 'computer';

   The result of this pass through the subquery is average sales of 25667 for computer books. Because book T03’s sales of 25667 equals the average (it’s the only computer book), T03 is displayed in the result.

4. The DBMS repeats this process until every row in the outer table candidate has been tested.

If you can get the same result by using a simple subquery or a correlated subquery, then use the simple subquery because it probably will run faster. Listings 8.14a and 8.14b show two equivalent queries that list all authors who earn 100 percent (1.0) of the royalty share on a book. Listing 8.14a, which uses a simple subquery, is more efficient than Listing 8.14b, which uses a correlated subquery. In the simple subquery, the DBMS reads the inner table title_authors once. In the correlated subquery, the DBMS must loop through title_authors five times—once for each qualifying row in the outer table authors. See Figure 8.14 for the result.

Why do I say that a statement that uses a simple subquery probably will run faster than an equivalent statement that uses a correlated subquery when a correlated subquery clearly requires more work? Because your DBMS’s optimizer might be clever enough to recognize and reformulate a correlated subquery as a semantically equivalent simple subquery internally before executing the statement. For more information, see “Comparing Equivalent Queries” later in this chapter.

Tips for Simple and Correlated Subqueries

• MySQL 4.0 and earlier don’t support subqueries; see the DBMS tip in “Tips for Subqueries” earlier in this chapter.

  In older PostgreSQL versions, convert the floating-point numbers in Listings 8.14a and 8.14b to DECIMAL; see “Converting Data Types with CAST()” in Chapter 5. To run Listings 8.14a and 8.14b, change the floating-point literal in each listing to:

  CAST(1.0 AS DECIMAL)

Qualifying Column Names in Subqueries

Recall from “Qualifying Column Names” in Chapter 7 that you can qualify a column name explicitly with a table name to identify the column unambiguously. In statements that contain subqueries, column names are qualified implicitly by the table referenced in the FROM clause at the same nesting level.

In Listing 8.15a, which lists the names of biography publishers, the column names are qualified implicitly, meaning:

• The column pub_id in the outer query’s WHERE clause is qualified implicitly by the table publishers in the outer query’s FROM clause.
• The column pub_id in the subquery’s SELECT clause is qualified implicitly by the table titles in the subquery’s FROM clause.

Listing 8.15b shows Listing 8.15a with explicit qualifiers. See Figure 8.15 for the result.

Tips for Qualified Column Names in Subqueries

• It’s never wrong to state a table name explicitly.
• You can use explicit qualifiers to override SQL’s default assumptions about table names and specify that a column is to match a table at a nesting level outside the column’s own level.
• If a column name can match more than one table at the same nesting level, then the column name is ambiguous, and you must qualify it with a table name (or table alias).
• MySQL 4.0 and earlier don’t support subqueries; see the DBMS tip in “Tips for Subqueries” earlier in this chapter.

Nulls in Subqueries

Beware of nulls; their presence complicates subqueries. If you don’t eliminate them when they’re present, then you might get an unexpected answer.

A subquery can hide a comparison to a null. Recall from “Nulls” in Chapter 3 that nulls don’t equal each other and that you can’t determine whether a null matches any other value. The following example involves a NOT IN subquery (see “Testing Set Membership with IN” later in this chapter). Consider the following two tables, each with one column. The first table is named table1:

col
----
   1
   2

The second table is named table2:

col
----
   1
   2
   3

If I run Listing 8.16 to list the values in table2 that aren’t in table1, then I get Figure 8.16a, as expected.

Now add a null to table1:

col
----
   1
   2
NULL

If I rerun Listing 8.16, then I get Figure 8.16b (an empty table), which is correct logically but not what I expected.

Why is the result empty this time? The solution requires some algebra. I can move the NOT outside the subquery condition without changing the meaning of Listing 8.16:

SELECT col
  FROM table2
  WHERE NOT col IN
    (SELECT col FROM table1);

The IN clause determines whether a value in table2 matches any value in table1, so I can rewrite the subquery as a compound condition:

SELECT col
  FROM table2
  WHERE NOT ((col = 1)
         OR  (col = 2)
         OR  (col = NULL));

If I apply De Morgan’s Laws (refer to Table 4.6 in Chapter 4), then this query becomes:

SELECT col
  FROM table2
  WHERE (col <> 1)
    AND (col <> 2)
    AND (col <> NULL);

The final expression, col <> NULL, always is unknown. Refer to the AND truth table (Table 4.3 in Chapter 4), and you’ll see that the entire WHERE search condition reduces to unknown, which always is rejected by WHERE.

To fix Listing 8.16 so that it doesn’t examine the null in table1, add an IS NOT NULL condition to the subquery (see “Testing for Nulls with IS NULL” in Chapter 4):

SELECT col
  FROM table2
  WHERE col NOT IN
    (SELECT col
       FROM table1
       WHERE col IS NOT NULL);

Tips for Nulls in Subqueries

• MySQL 4.0 and earlier don’t support subqueries; see the DBMS tip in “Tips for Subqueries” earlier in this chapter.

Using Subqueries as Column Expressions

In Chapters 4, 5, and 6, you learned that the items in a SELECT-clause list can be literals, column names, or more-complex expressions. SQL also lets you to embed a subquery in a SELECT-clause list.

A subquery that’s used as a column expression must be a scalar subquery. Recall from Table 8.1 in “Subquery Syntax” earlier in this chapter that a scalar subquery returns a single value (that is, a one-row, one-column result). In most cases, you’ll have to use an aggregate function or restrictive WHERE conditions in the subquery to guarantee that the subquery returns only one row.

The syntax for the SELECT-clause list is the same as you’ve been using all along, except that you can specify a parenthesized subquery as one of the column expressions in the list, as the following examples show.

Listing 8.17 uses two simple subqueries as column expressions to list each biography, its price, the average price of all books (not just biographies), and the difference between the price of the biography and the average price of all books. The aggregate function AVG() guarantees that each subquery returns a single value. See Figure 8.17 for the result. Remember that AVG() ignores nulls when computing an average; see “Calculating an Average with AVG()” in Chapter 6.

Listing 8.18 uses correlated subqueries to list all the authors of each book in one row, as you’d view them in a report or spreadsheet. See Figure 8.18 for the result. Note that in each WHERE clause, SQL qualifies title_id implicitly with the table alias ta referenced in the subquery’s FROM clause; see “Qualifying Column Names in Subqueries” earlier in this chapter. For a more efficient way to implement this query, see “Tips for Column Expressions” later in this section. See Listing 15.8 in Chapter 15 for the reverse of this query.

In Listing 8.19, I revisit Listing 7.30 in “Creating Outer Joins with OUTER JOIN” in Chapter 7, but this time, I’m using a correlated subquery instead of an outer join to list the number of books that each author wrote (or cowrote). See Figure 8.19 for the result.

Listing 8.20 uses a correlated subquery to list each author and the latest date on which he or she published a book. You should qualify every column name explicitly in a subquery that contains a join to make it clear which table is referenced (even when qualifiers are unnecessary). See Figure 8.20 for the result.

Listing 8.21 uses a correlated subquery to compute the running total of all book sales. A running total, or running sum, is a common calculation: For each book, I want to compute the sum of all sales of the books that precede the book. Here, I’m defining precede to mean those books whose title_id comes before the current book’s title_id alphabetically. Note the use of table aliases to refer to the same table in two contexts. The subquery returns the sum of sales for all books preceding the current book, which is denoted by t1.title_id. See Figure 8.21 for the result. See also “Calculating Running Statistics” in Chapter 15.

You also can use a subquery in a FROM clause. In “Tips for DISTINCT” in Chapter 6, I used a FROM subquery to replicate a distinct aggregate function. Listing 8.22 uses a FROM subquery to calculate the greatest number of titles written (or cowritten) by any author. See Figure 8.22 for the result. Note that the outer query uses a table alias (ta) and column alias (count_titles) to reference the inner query’s result. See also “Column aliases and WHERE” in Chapter 4.

Tips for Column Expressions

• You also can use a subquery as a column expression in UPDATE, INSERT, and DELETE statements (see Chapter 10) but not in an ORDER BY list.

• Use CASE expressions instead of correlated subqueries to implement Listing 8.18 more efficiently (see “Evaluating Conditional Values with CASE” in Chapter 5):

  SELECT title_id,
    MIN(CASE au_order WHEN 1
          THEN au_id END)
      AS "Author 1",
    MIN(CASE au_order WHEN 2
          THEN au_id END)
      AS "Author 2",
    MIN(CASE au_order WHEN 3
          THEN au_id END)
      AS "Author 3"
  FROM title_authors
  GROUP BY title_id
  ORDER BY title_id ASC;

• MySQL 4.0 and earlier don’t support subqueries; see the DBMS tip in “Tips for Subqueries” earlier in this chapter.

  In Microsoft Access, you must increase the precision of the average-price calculation in Listing 8.17. Use the type-conversion function CDbl() to coerce the average price to a double-precision floating-point number; see the DBMS tip in “Tips for Converting Data Types” in Chapter 5. To run Listing 8.17, change both occurrences of AVG(price) to CDbl(AVG(price)).

Comparing a Subquery Value by Using a Comparison Operator

You can use a subquery as a filter in a WHERE clause or HAVING clause by using one of the comparison operators (=, <>, <, <=, >, or >=).

The important characteristics of a subquery comparison test are:

• The comparison operators work the same way as they do in other comparisons (refer to Table 4.2 in Chapter 4).
• The subquery can be simple or correlated (see “Simple and Correlated Subqueries” earlier in this chapter).
• The subquery’s SELECT-clause list can include only one expression or column name.
• The compared values must have the same data type or must be implicitly convertible to the same type (see “Converting Data Types with CAST()” in Chapter 5).
• String comparisons are case insensitive or case sensitive, depending on your DBMS; see the DBMS tip in “Tips for WHERE” in Chapter 4.
• The subquery must return a single value (a one-row, one-column result). A subquery that returns more than one value will cause an error.
• If the subquery result contains zero rows, then the comparison test will evaluate to false.

The hard part of writing these statements is getting the subquery to return one value, which you can guarantee several ways:

• Using an aggregate function on an ungrouped table always returns a single value (see Chapter 6).
• Using a join with the outer query based on a key always returns a single value.

To compare a subquery value:

• In the WHERE clause of a SELECT statement, type:

  WHERE test_expr op (subquery)

  test_expr is a literal value, a column name, an expression, or a subquery that returns a single value; op is a comparison operator (=, <>, <, <=, >, or >=); and subquery is a scalar subquery that returns exactly one column and zero or one rows.

  If the value returned by subquery satisfies the comparison to test_expr, then the comparison condition evaluates to true. The comparison condition is false if the subquery value doesn’t satisfy the condition, the subquery value is null, or the subquery result is empty (has zero rows).

  The same syntax applies to a HAVING clause:

  HAVING test_expr op (subquery)

Listing 8.23 tests the result of a simple subquery for equality to list the authors who live in the state in which Tenterhooks Press is located. Only one publisher is named Tenterhooks Press, so the inner WHERE condition guarantees that the inner query returns a single-valued result. See Figure 8.23 for the result.

Listing 8.24 is the same as Listing 8.23 except for the name of the publisher. No publisher named XXX exists, so the subquery returns an empty table (zero rows). The comparison evaluates to null, so the final result is empty. See Figure 8.24 for the result.

Listing 8.25 lists the books with above-average sales. Subqueries introduced with comparison operators often use aggregate functions to return a single value. See Figure 8.25 for the result.

To list the authors of the books with above-average sales, I’ve added an inner join to Listing 8.25 (Listing 8.26 and Figure 8.26).

Recall from the introduction to this chapter that you can use a subquery almost anywhere an expression is allowed, so this syntax is valid:

WHERE (subquery) op (subquery)

The left subquery must return a single value. Listing 8.27 is equivalent to Listing 8.26, but I’ve removed the inner join and instead placed a correlated subquery to the left of the comparison operator. See Figure 8.27 for the result.

You can include GROUP BY or HAVING clauses in a subquery if you know that the GROUP BY or HAVING clause itself returns a single value. Listing 8.28 lists the books priced higher than the highest-priced biography. See Figure 8.28 for the result.

Listing 8.29 uses a subquery in a HAVING clause to list the publishers whose average sales exceed overall average sales. Again, the subquery returns a single value (the average of all sales). See Figure 8.29 for the result.

Listing 8.30 uses a correlated subquery to list authors whose royalty share is less than the highest royalty share of any coauthor of a book. The outer query selects the rows of title_authors (that is, of ta1) one by one. The subquery calculates the highest royalty share for each book being considered for selection in the outer query. For each possible value of ta1, the DBMS evaluates the subquery and puts the row being considered in the result if the royalty share is less than the calculated maximum. See Figure 8.30 for the result.

Listing 8.31 uses a correlated subquery to imitate a GROUP BY clause and list all books that have a price greater than the average for books of its type. For each possible value of t1, the DBMS evaluates the subquery and includes the row in the result if the price value in that row exceeds the calculated average. It’s unnecessary to group by type explicitly because the rows for which the average price is calculated are restricted by the subquery’s WHERE clause. See Figure 8.31 for the result.

Listing 8.32 uses the same structure as Listing 8.31 to list all the books whose sales are less than the best-selling books of their types. See Figure 8.32 for the result.

Tips for Subquery Comparison Operators

• If a subquery returns more than one row, then you can use ALL or ANY to modify the comparison operator, or you can introduce the subquery with IN. (ALL, ANY, and IN are covered later in this chapter.)
• MySQL 4.0 and earlier don’t support subqueries; see the DBMS tip in “Tips for Subqueries” earlier in this chapter.

Testing Set Membership with IN

“List Filtering with IN” in Chapter 4 describes how to use the IN keyword in a WHERE clause to compare a literal, column value, or more-complex expression to a list of values. You also can use a subquery to generate the list.

The important characteristics of a subquery set membership test are:

• IN works the same way with the values in a subquery result as it does with a parenthesized list of values (see “List Filtering with IN” in Chapter 4).
• The subquery can be simple or correlated (see “Simple and Correlated Subqueries” earlier in this chapter).
• The subquery’s SELECT-clause list can include only one expression or column name.
• The compared values must have the same data type or must be implicitly convertible to the same type (see “Converting Data Types with CAST()” in Chapter 5).
• String comparisons are case insensitive or case sensitive, depending on your DBMS; see the DBMS tip in “Tips for WHERE” in Chapter 4.
• The subquery must return exactly one column and zero or more rows. A subquery that returns more than one column will cause an error.
• You can use NOT IN to reverse the effect of the IN test. If you specify NOT IN, then the DBMS takes the action specified by the SQL statement if there is no matching value in the subquery’s result.

To test set membership:

• In the WHERE clause of a SELECT statement, type:

  WHERE test_expr [NOT] IN (subquery)

  test_expr is a literal value, a column name, an expression, or a subquery that returns a single value; and subquery is a subquery that returns one column and zero or more rows.

  If the value of test_expr equals any value returned by subquery, then the IN condition evaluates to true. The IN condition is false if the subquery result is empty, if no row in the subquery result matches test_expr, or if all the values in the subquery result are null. Specify NOT to negate the condition’s result.

  The same syntax applies to a HAVING clause:

  HAVING test_expr [NOT] IN (subquery)

Listing 8.33 lists the names of the publishers that have published biographies. The DBMS evaluates this statement in two steps. First, the inner query returns the IDs of the publishers that have published biographies (P01 and P03). Second, the DBMS substitutes these values into the outer query, which finds the names that go with the IDs in the table publishers. See Figure 8.33 for the result.

Here’s the join version of Listing 8.33:

SELECT DISTINCT pub_name
  FROM publishers p
  INNER JOIN titles t
    ON p.pub_id = t.pub_id
  AND type = 'biography';

Listing 8.34 is the same as Listing 8.33, except that it uses NOT IN to list the names of the publishers that haven’t published biographies. See Figure 8.34 for the result. This statement can’t be converted to a join. The analogous not-equal join has a different meaning: It lists the names of publishers that have published some book that isn’t a biography.

Listing 8.35 is equivalent to Listing 7.31 in Chapter 7, except that it uses a subquery instead of an outer join to list the authors who haven’t written (or cowritten) a book. See Figure 8.35 for the result.

Listing 8.36 lists the names of the authors who have published a book with publisher P03 (Schadenfreude Press). The join to the table authors is necessary to include the authors’ names (not just their IDs) in the result. See Figure 8.36 for the result.

A subquery can itself include one or more subqueries. Listing 8.37 lists the names of authors who have participated in writing at least one biography. The innermost query returns the title IDs T06, T07, T10, and T12. The DBMS evaluates the subquery at the next higher level by using these title IDs and returns the author IDs. Finally, the outermost query uses the author IDs to find the names of the authors. See Figure 8.37 for the result.

Excessive subquery nesting makes a statement hard to read; often, it’s easier to restate the query as a join. Here’s the join version of Listing 8.37:

SELECT DISTINCT a.au_id, au_fname, au_lname
  FROM authors a
  INNER JOIN title_authors ta
    ON a.au_id = ta.au_id
  INNER JOIN titles t
    ON t.title_id = ta.title_id
  WHERE type = 'biography';

Listing 8.38 lists the names of all non-lead authors (au_order > 1) who live in California and who receive less than 50 percent of the royalties for a book. See Figure 8.38 for the result.

Here’s the join version of Listing 8.38:

SELECT DISTINCT a.au_id, au_fname, au_lname
  FROM authors a
  INNER JOIN title_authors ta
    ON a.au_id = ta.au_id
  WHERE state = 'CA'
    AND royalty_share < 0.5
    AND au_order > 1;

Listing 8.39 lists the names of authors who are coauthors of a book. To determine whether an author is a coauthor or the sole author of a book, examine his or her royalty share for the book. If the royalty share is less than 100 percent (1.0), then the author is a coauthor; otherwise, he or she is the sole author. See Figure 8.39 for the result.

Listing 8.40 uses a correlated subquery to list the names of authors who are sole authors of a book—that is, authors who earn 100 percent (1.0) of the royalty on a book. See Figure 8.40 for the result. The DBMS considers each row in the outer-query table authors to be a candidate for inclusion in the result. When the DBMS examines the first candidate row in authors, it sets the correlation variable a.au_id equal to A01 (Sarah Buchman), which it substitutes into the inner query:

SELECT royalty_share
  FROM title_authors ta
  WHERE ta.au_id = 'A01';

The inner query returns 1.0, so the outer query evaluates to:

SELECT a.au_id, au_fname, au_lname
  FROM authors a
  WHERE 1.0 IN (1.0)

The WHERE condition is true, so author A01 is included in the result. The DBMS repeats this procedure for every author; see “Simple and Correlated Subqueries” earlier in this chapter.

Listing 8.41 lists the names of authors who are both coauthors and sole authors. The inner query returns the author IDs of sole authors, and the outer query compares these IDs with the IDs of the coauthors. See Figure 8.41 for the result.

You can rewrite Listing 8.41 as a join or as an intersection. Here’s the join version of Listing 8.41:

SELECT DISTINCT a.au_id, au_fname, au_lname
  FROM authors a
  INNER JOIN title_authors ta1
    ON a.au_id = ta1.au_id
  INNER JOIN title_authors ta2
    ON a.au_id = ta2.au_id
  WHERE ta1.royalty_share < 1.0
    AND ta2.royalty_share = 1.0;

Here’s the intersection version of Listing 8.41 (see “Finding Common Rows with INTERSECT” in Chapter 9):

SELECT DISTINCT a.au_id, au_fname, au_lname
  FROM authors a
  INNER JOIN title_authors ta
    ON a.au_id = ta.au_id
  WHERE ta.royalty_share < 1.0
INTERSECT
SELECT DISTINCT a.au_id, au_fname, au_lname
  FROM authors a
  INNER JOIN title_authors ta
    ON a.au_id = ta.au_id
  WHERE ta.royalty_share = 1.0;

Listing 8.42 uses a correlated subquery to list the types of books published by more than one publisher. See Figure 8.42 for the result.

Here’s the self-join version of Listing 8.42:

SELECT DISTINCT t1.type
  FROM titles t1
  INNER JOIN titles t2
    ON t1.type = t2.type
    AND t1.pub_id <> t2.pub_id;

Tips for IN

• IN is equivalent to =ANY; see “Comparing Some Subquery Values with ANY” later in this chapter.
• NOT IN is equivalent to <>ALL (not <>ANY); see “Comparing All Subquery Values with ALL” later in this chapter.

• To run Listing 8.41 in Microsoft Access, type:

  SELECT DISTINCT a.au_id, au_fname, au_lname
  FROM (authors AS a
    INNER JOIN title_authors AS ta1
      ON a.au_id = ta1.au_id)
  INNER JOIN title_authors AS ta2
    ON a.au_id = ta2.au_id
  WHERE ta1.royalty_share < 1.0
    AND ta2.royalty_share = 1.0;

  MySQL 4.0 and earlier don’t support subqueries; see the DBMS tip in “Tips for Subqueries” earlier in this chapter.

  In older PostgreSQL versions, convert the floating-point numbers in Listings 8.38 through 8.41 to DECIMAL; see “Converting Data Types with CAST()” in Chapter 5. To run Listings 8.38 through 8.41, change the floating-point literals to (Listing 8.38):

  CAST(0.5 AS DECIMAL)

  and (Listing 8.39):

  CAST(1.0 AS DECIMAL)

  and (Listing 8.40):

  CAST(1.0 AS DECIMAL)

  and (Listing 8.41):

  CAST(1.0 AS DECIMAL) (in two places)

  Some DBMSs let you test multiple values simultaneously by using this syntax:

  SELECT columns
  FROM table1
  WHERE (col1, col2,..., colN) IN
    (SELECT colA, colB,..., colN
       FROM table2);

  The test expression (left of IN) is a parenthesized list of table1 columns. The subquery returns the same number of columns as there are in the list. The DBMS compares the values in corresponding columns. The following query, for example, works in Oracle, Db2, MySQL, and PostgreSQL:

  SELECT au_id, city, state
  FROM authors
  WHERE (city, state) IN
    (SELECT city, state
       FROM publishers);

  The result lists the authors who live in the same city and state as some publisher:

  au_id city          state
----- ------------- -----
A03   San Francisco CA
A04   San Francisco CA
A05   New York      NY

Comparing All Subquery Values with ALL

You can use the ALL keyword to determine whether a value is less than or greater than all the values in a subquery result.

The important characteristics of subquery comparisons that use ALL are:

• ALL modifies a comparison operator in a subquery comparison test and follows =, <>, <, <=, >, or >=; see “Comparing a Subquery Value by Using a Comparison Operator” earlier in this chapter.
• The combination of a comparison operator and ALL tells the DBMS how to apply the comparison test to the values returned by a subquery. <ALL, for example, means less than every value in the subquery result, and >ALL means greater than every value in the subquery result.

• When ALL is used with <, <=, >, or >=, the comparison is equivalent to evaluating the subquery result’s minimum or maximum value. <ALL means less than every subquery value—in other words, less than the minimum value. >ALL means greater than every subquery value—that is, greater than the maximum value. Table 8.2 shows equivalent ALL expressions and column functions. Listing 8.45 later in this section shows how to replicate a >ALL query by using MAX().

  Table 8.2ALL Equivalencies

  ALL Expression	Column Function
  < ALL(subquery)	< MIN(subquery values)
  > ALL(subquery)	> MAX(subquery values)

• Semantic equivalence doesn’t mean that two queries will run at the same speed. For example, the query

  SELECT * FROM table1
  WHERE col1 > ANY
    (SELECT MAX(col1) FROM table2);

  usually is faster than

  SELECT * FROM table1
  WHERE col1 > ALL
    (SELECT col1 FROM table2);

  For more information, see “Comparing Equivalent Queries” later in this chapter.

• The comparison =ALL is valid but isn’t often used. =ALL always will be false unless all the values returned by the subquery are identical (and equal to the test value).
• The subquery can be simple or correlated (see “Simple and Correlated Subqueries” earlier in this chapter).
• The subquery’s SELECT-clause list can include only one expression or column name.
• The compared values must have the same data type or must be implicitly convertible to the same type (see “Converting Data Types with CAST()” in Chapter 5).
• String comparisons are case insensitive or case sensitive, depending on your DBMS; see the DBMS tip in “Tips for WHERE” in Chapter 4.
• The subquery must return exactly one column and zero or more rows. A subquery that returns more than one column will cause an error.
• If the subquery returns no rows, then the ALL condition is true. (You might find this result to be counterintuitive.)

To compare all subquery values:

• In the WHERE clause of a SELECT statement, type:

  WHERE test_expr op ALL (subquery)

  test_expr is a literal value, a column name, an expression, or a subquery that returns a single value; op is a comparison operator (=, <>, <, <=, >, or >=); and subquery is a subquery that returns one column and zero or more rows.

  The ALL condition evaluates to true if all values in subquery satisfy the ALL condition or if the subquery result is empty (has zero rows). The ALL condition is false if any (at least one) value in subquery doesn’t satisfy the ALL condition or if any value is null.

  The same syntax applies to a HAVING clause:

  HAVING test_expr op ALL (subquery)

Listing 8.43 lists the authors who live in a city in which no publisher is located. The inner query finds all the cities in which publishers are located, and the outer query compares each author’s city to all the publishers’ cities. See Figure 8.43 for the result.

You can use NOT IN to replicate Listing 8.43:

SELECT au_id, au_lname, au_fname, city
  FROM authors
  WHERE city NOT IN
    (SELECT city FROM publishers);

Listing 8.44 lists the nonbiographies that are priced less than all the biographies. The inner query finds all the biography prices. The outer query inspects the lowest price in the list and determines whether each non-biography is cheaper. See Figure 8.44 for the result. The price IS NOT NULL condition is required because the price of biography T10 is null. Without this condition, the entire query would return zero rows because it’s impossible to determine whether a price is less than null (see “Nulls” in Chapter 3).

Listing 8.45 lists the books that outsold all the books that author A06 wrote (or cowrote). The inner query uses a join to find the sales of each book by author A06. The outer query inspects the highest sales figure in the list and determines whether each book sold more copies. See Figure 8.45 for the result. Again, the IS NOT NULL condition is needed in case sales is null for a book by author A06.

I can replicate Listing 8.45 by using GROUP BY, HAVING, and MAX() (instead of ALL):

SELECT title_id
  FROM titles
  GROUP BY title_id
  HAVING MAX(sales) >
    (SELECT MAX(sales)
       FROM title_authors ta
       INNER JOIN titles t
         ON t.title_id = ta.title_id
       WHERE ta.au_id = 'A06');

Listing 8.46 uses a correlated subquery in the HAVING clause of the outer query to list the types of books for which the highest sales figure is more than twice the average sales for that type. The inner query is evaluated once for each group defined in the outer query (once for each type of book). See Figure 8.46 for the result.

Tips for ALL

• <>ALL is equivalent to NOT IN; see “Testing Set Membership with IN” earlier in this chapter.

• MySQL 4.0 and earlier don’t support subqueries; see the DBMS tip in “Tips for Subqueries” earlier in this chapter.

  In older PostgreSQL versions, convert the floating-point numbers in Listing 8.46 to DECIMAL; see “Converting Data Types with CAST()” in Chapter 5. To run Listing 8.46, change the floating-point literal to:

  CAST(2.0 AS DECIMAL)

Comparing Some Subquery Values with ANY

ANY works like ALL (see the preceding section) but instead determines whether a value is equal to, less than, or greater than any (at least one) of the values in a subquery result.

The important characteristics of subquery comparisons that use ANY are:

• ANY modifies a comparison operator in a subquery comparison test and follows =, <>, <, <=, >, or >=; see “Comparing a Subquery Value by Using a Comparison Operator” earlier in this chapter.
• The combination of a comparison operator and ANY tells the DBMS how to apply the comparison test to the values returned by a subquery. <ANY, for example, means less than at least one value in the subquery result, and >ANY means greater than at least one value in the subquery result.

• When ANY is used with <, <=, >, or >=, the comparison is equivalent to evaluating the subquery result’s maximum or minimum value. <ANY means less than at least one subquery value—in other words, less than the maximum value. >ANY means greater than at least one subquery value—that is, greater than the minimum value. Table 8.3 shows equivalent ANY expressions and column functions. Listing 8.49 later in this section shows how to replicate a >ANY query by using MIN().

  Table 8.3ANY Equivalencies

  ANY Expression	Column Function
  < ANY(subquery)	< MAX(subquery values)
  > ANY(subquery)	> MIN(subquery values)

• The comparison =ANY is equivalent to IN; see “Testing Set Membership with IN” earlier in this chapter.
• The subquery can be simple or correlated (see “Simple and Correlated Subqueries” earlier in this chapter).
• The subquery’s SELECT-clause list can include only one expression or column name.
• The compared values must have the same data type or must be implicitly convertible to the same type (see “Converting Data Types with CAST()” in Chapter 5).
• String comparisons are case insensitive or case sensitive, depending on your DBMS; see the DBMS tip in “Tips for WHERE” in Chapter 4.
• The subquery must return exactly one column and zero or more rows. A subquery that returns more than one column will cause an error.
• If the subquery returns no rows, then the ANY condition is false.

To compare some subquery values:

• In the WHERE clause of a SELECT statement, type:

  WHERE test_expr op ANY (subquery)

  test_expr is a literal value, a column name, an expression, or a subquery that returns a single value; op is a comparison operator (=, <>, <, <=, >, or >=); and subquery is a subquery that returns one column and zero or more rows.

  If any (at least one) value in subquery satisfies the ANY condition, then the condition evaluates to true. The ANY condition is false if no value in subquery satisfies the condition or if subquery is empty (has zero rows) or contains all nulls.

  The same syntax applies to a HAVING clause:

  HAVING test_expr op ANY (subquery)

Listing 8.47 lists the authors who live in a city in which a publisher is located. The inner query finds all the cities in which publishers are located, and the outer query compares each author’s city to all the publishers’ cities. See Figure 8.47 for the result.

You can use IN to replicate Listing 8.47:

SELECT au_id, au_lname, au_fname, city
FROM authors
  WHERE city IN
    (SELECT city FROM publishers);

Listing 8.48 lists the nonbiographies that are priced less than at least one biography. The inner query finds all the biography prices. The outer query inspects the highest price in the list and determines whether each nonbiography is cheaper. See Figure 8.48 for the result.

Unlike the ALL comparison in Listing 8.44 in the preceding section, the price IS NOT NULL condition isn’t required here, even though the price of biography T10 is null. The DBMS doesn’t determine whether all the price comparisons are true—just whether at least one is true—so the null comparison is ignored.

Listing 8.49 lists the books that outsold at least one of the books that author A06 wrote (or cowrote). The inner query uses a join to find the sales of each book by author A06. The outer query inspects the lowest sales figure in the list and determines whether each book sold more copies. See Figure 8.49 for the result. Again, unlike the ALL comparison in Listing 8.45 in the preceding section, the IS NOT NULL condition isn’t needed here.

I can replicate Listing 8.49 by using GROUP BY, HAVING, and MIN() (instead of ANY):

SELECT title_id
  FROM titles
  GROUP BY title_id
  HAVING MIN(sales) >
    (SELECT MIN(sales)
       FROM title_authors ta
       INNER JOIN titles t
         ON t.title_id = ta.title_id
       WHERE ta.au_id = 'A06');

Tips for ANY

• =ANY is equivalent to IN, but <>ANY isn’t equivalent to NOT IN. If subquery returns the values x, y, and z, then

  test_expr <> ANY (subquery)

  is equivalent to:

  test_expr <> x OR
test_expr <> y OR
test_expr <> z

  But

  test_expr NOT IN (subquery)

  is equivalent to:

  test_expr <> x AND
test_expr <> y AND
test_expr <> z

  (NOT IN actually is equivalent to <>ALL.)

• In the SQL standard, the keywords ANY and SOME are synonyms. In some DBMSs, you can use SOME in place of ANY.
• MySQL 4.0 and earlier don’t support subqueries; see the DBMS tip in “Tips for Subqueries” earlier in this chapter.

Testing Existence with EXISTS

So far in this chapter, I’ve been using the comparison operators IN, ALL, and ANY to compare a specific test value to values in a subquery result. EXISTS and NOT EXISTS don’t compare values; rather, they simply look for the existence or nonexistence of rows in a subquery result.

The important characteristics of an existence test are:

• An existence test doesn’t compare values, so it isn’t preceded by a test expression.
• The subquery can be simple or correlated but usually is correlated (see “Simple and Correlated Subqueries” earlier in this chapter).
• The subquery can return any number of columns and rows.
• By convention, the SELECT clause in the subquery is SELECT * to retrieve all columns. Listing specific column names is unnecessary because EXISTS simply tests for the existence of rows that satisfy the subquery conditions; the actual values in the rows are irrelevant.
• All IN, ALL, and ANY queries can be expressed with EXISTS or NOT EXISTS. I’ll give equivalent queries in some of the examples later in this section.
• If the subquery returns at least one row, then an EXISTS test is true, and a NOT EXISTS test is false.
• If the subquery returns no rows, then an EXISTS test is false, and a NOT EXISTS test is true.
• A subquery row that contains only nulls counts as a row. (An EXISTS test is true, and a NOT EXISTS test is false.)
• Because an EXISTS test performs no comparisons, it’s not subject to the same problems with nulls as tests that use IN, ALL, or ANY; see “Nulls in Subqueries” earlier in this chapter.

To test existence:

• In the WHERE clause of a SELECT statement, type:

  WHERE [NOT] EXISTS (subquery)

  subquery is a subquery that returns any number of columns and rows.

  If subquery returns one or more rows, then the EXISTS test evaluates to true. If subquery returns zero rows, then the EXISTS test evaluates to false. Specify NOT to negate the test’s result.

  The same syntax applies to a HAVING clause:

  HAVING [NOT] EXISTS (subquery)

Listing 8.50 lists the names of the publishers that have published biographies. This query considers each publisher’s ID in turn and determines whether it causes the existence test to evaluate to true. Here, the first publisher is P01 (Abatis Publishers). The DBMS ascertains whether any rows exist in the table titles in which pub_id is P01 and type is biography. If so, then Abatis Publishers is included in the final result. The DBMS repeats the same process for each of the other publisher IDs. See Figure 8.50 for the result. If I wanted to list the names of publishers that haven’t published biographies, then I’d change EXISTS to NOT EXISTS. See Listing 8.33 earlier in this chapter for an equivalent query that uses IN.

Listing 8.51 lists the authors who haven’t written (or cowritten) a book. See Figure 8.51 for the result. See Listing 8.35 earlier in this chapter for an equivalent query that uses NOT IN.

Listing 8.52 lists the authors who live in a city in which a publisher is located. See Figure 8.52 for the result. See Listing 8.47 earlier in this chapter for an equivalent query that uses =ANY.

“Finding Common Rows with INTERSECT” in Chapter 9 describes how to use INTERSECT to retrieve the rows that two tables have in common. You also can use EXISTS to find an intersection. Listing 8.53 lists the cities in which both an author and publisher are located. See Figure 8.53 for the result. See Listing 9.8 in Chapter 9 for an equivalent query that uses INTERSECT.

You also can replicate this query with an inner join:

SELECT DISTINCT a.city
  FROM authors a
  INNER JOIN publishers p
    ON a.city = p.city;

“Finding Different Rows with EXCEPT” in Chapter 9 describes how to use EXCEPT to retrieve the rows in one table that aren’t also in another table. You also can use NOT EXISTS to find a difference. Listing 8.54 lists the cities in which an author lives but a publisher isn’t located. See Figure 8.54 for the result. See Listing 9.9 in Chapter 9 for an equivalent query that uses EXCEPT.

You also can replicate this query with NOT IN:

SELECT DISTINCT city
  FROM authors
  WHERE city NOT IN
    (SELECT city
       FROM publishers);

Or with an outer join:

SELECT DISTINCT a.city
  FROM authors a
  LEFT OUTER JOIN publishers p
    ON a.city = p.city
  WHERE p.city IS NULL;

Listing 8.55 lists the authors who wrote (or cowrote) three or more books. See Figure 8.55 for the result.

Listing 8.56 uses two existence tests to list the authors who wrote (or cowrote) both children’s and psychology books. See Figure 8.56 for the result.

Listing 8.57 performs a uniqueness test to determine whether duplicates occur in the column au_id in the table authors. The query prints Yes if duplicate values exist in the column au_id; otherwise, it returns an empty result. See Figure 8.57 for the result. au_id is the primary key of authors, so of course it contains no duplicates.

Listing 8.58 shows the same query for the table title_authors, which does contain duplicate au_id values. See Figure 8.58 for the result. You can add grouping columns to the GROUP BY clause to determine whether multiple-column duplicates exist.

Tips for EXISTS

• You also can use COUNT(*) to determine whether a subquery returns at least one row, but COUNT(*) (usually) is less efficient than EXISTS. The DBMS quits processing an EXISTS subquery as soon as it determines whether the subquery returns a row, whereas COUNT(*) forces the DBMS to process the entire subquery. This query is equivalent to Listing 8.52 but runs slower:

  SELECT au_id, au_lname, au_fname, city
  FROM authors a
  WHERE
  (SELECT COUNT(*)
     FROM publishers p
     WHERE p.city = a.city) > 0;

• Although SELECT * is the most common form of the SELECT clause in an EXISTS subquery, you can use SELECT column or SELECT constant_value to speed queries if your DBMS’s optimizer isn’t bright enough to figure out that it doesn’t need to construct an entire interim table for an EXISTS subquery. For more information, see “Comparing Equivalent Queries” later in this chapter.

• Although I use SELECT COUNT(*) in some of the DBMS-specific subqueries in the DBMS tip in this section, you should be wary of using an aggregate function in a subquery’s SELECT clause. The existence test in Listing 8.59, for example, always is true because COUNT(*) always will return a row (with the value zero here). I could argue that the result, Figure 8.59, is flawed logically because no publisher ID XXX exists.

  Listing 8.59Be careful when using aggregate functions in a subquery SELECT clause. See Figure 8.59 for the result.

  SELECT pub_id
  FROM publishers
  WHERE EXISTS
    (SELECT COUNT(*)
       FROM titles
       WHERE pub_id = 'XXX');

  Figure 8.59Result of Listing 8.59.

  pub_id
------
P01
P02
P03
P04

• To run Listings 8.55, 8.57, and 8.58 in Microsoft Access, change SELECT * to SELECT 1. Additionally, in Listing 8.57 add the clause FROM authors to the outer query, and in Listing 8.58 add the clause FROM title_authors to the outer query.

  To run Listings 8.57 and 8.58 in Oracle, add the clause FROM DUAL to the outer query; see the DBMS tip in “Tips for Derived Columns” in Chapter 5.

  To run Listings 8.55, 8.57, and 8.58 in Db2, change SELECT * to SELECT 1. Additionally, in Listings 8.57 and 8.58, add the clause FROM SYSIBM.SYSDUMMY1 to the outer query; see the DBMS tip in “Tips for Derived Columns” in Chapter 5. For example, change Listing 8.57 to:

  SELECT DISTINCT 'Yes' AS "Duplicates?"
  FROM SYSIBM.SYSDUMMY1
  WHERE EXISTS
    (SELECT 1
       FROM authors
       GROUP BY au_id
       HAVING COUNT(*) > 1);

  In MySQL, to run Listing 8.57 add the clause FROM authors to the outer query, and in Listing 8.58 add the clause FROM title_authors to the outer query. MySQL 4.0 and earlier don’t support subqueries; see the DBMS tip in “Tips for Subqueries” earlier in this chapter.

  To run Listings 8.55, 8.57, and 8.58 in PostgreSQL, change SELECT * to SELECT 1.

Comparing Equivalent Queries

As you’ve seen in this chapter and the preceding one, you can express the same query in different ways (different syntax, same semantics). To expand on this point, I’ve written the same query six semantically equivalent ways. Each of the statements in Listing 8.60 lists the authors who have written (or cowritten) at least one book. See Figure 8.60 for the result.

The first two queries (inner joins) will run at the same speed as one another. Of the third through sixth queries (which use subqueries), the last one probably is the worst performer. The DBMS will stop processing the other subqueries as soon as it encounters a single matching value. But the subquery in the last statement has to count all the matching rows before it returns either true or false. Your DBMS’s optimizer should run the inner joins at about the same speed as the fastest subquery statement.

You might find this programming flexibility to be attractive, but people who design DBMS optimizers don’t, because they’re tasked with considering all the possible ways to express a query, figuring out which one performs best, and reformulating your query internally to its optimal form. (Entire careers are devoted to solving these types of optimization problems.) If your DBMS has a flawless optimizer, then it will run all six of the queries in Listing 8.60 at the same speed. But that situation is unlikely, so you’ll have to experiment with your DBMS to see which version runs fastest.

Tips for Comparing Equivalent Queries

• You should compare queries against large test tables (more than 10000 or even 100000 rows) so that speed and memory differences will be obvious.

• DBMSs provide tools to let you measure the efficiency of queries. Tables 8.4 and 8.5 list the commands that time queries and show their execution plans.

  Table 8.4Timing Queries

  DBMS	Command
  Access	Not available
  SQL Server	SET STATISTICS TIME ON
  Oracle	SET TIMING ON
  Db2	db2batch
  MySQL	The mysql command-line utility prints execution times by default.
  PostgreSQL	\timing

  Table 8.5Showing Query Execution Plans

  DBMS	Command
  Access	Not available
  SQL Server	SET SHOWPLAN_TEXT ON
  Oracle	EXPLAIN PLAN
  Db2	EXPLAIN or db2expln
  MySQL	EXPLAIN
  PostgreSQL	EXPLAIN