Informix Guide to SQL: Tutorial

Informix Guide to SQL: Tutorial
Chapter 3: Composing Advanced SELECT Statements

Home Contents Index Master Index New Book

Subqueries in SELECT Statements

A SELECT statement nested in the WHERE clause of another SELECT statement (or in an INSERT, DELETE, or UPDATE statement) is called a subquery. Each subquery must contain a SELECT clause and a FROM clause. A subquery must be enclosed in parentheses so that the database server performs that operation first.

Subqueries can be correlated or uncorrelated. A subquery (or inner SELECT statement) is correlated when the value that it produces depends on a value produced by the outer SELECT statement that contains it. Any other kind of subquery is considered uncorrelated.

The important feature of a correlated subquery is that, because it depends on a value from the outer SELECT, it must be executed repeatedly, once for every value that the outer SELECT produces. An uncorrelated subquery is executed only once.

You can construct a SELECT statement with a subquery to replace two separate SELECT statements.

Subqueries in SELECT statements allow you to perform the following actions:

Compare an expression to the result of another SELECT statement
Determine whether the results of another SELECT statement include an expression
Determine whether another SELECT statement selects any rows

An optional WHERE clause in a subquery is often used to narrow the search condition.

A subquery selects and returns values to the first or outer SELECT statement. A subquery can return no value, a single value, or a set of values:

If a subquery returns no value, the query does not return any rows. Such a subquery is equivalent to a null value.
If a subquery returns one value, the value is in the form of either one aggregate expression or exactly one row and one column. Such a subquery is equivalent to a single number or character value.
If a subquery returns a list or set of values, the values represent either one row or one column.

The following keywords introduce a subquery in the WHERE clause of a SELECT statement:

ALL
ANY
IN
EXISTS

You can use any relational operator with ALL and ANY to compare something to every one of (ALL) or to any one of (ANY) the values that the subquery produces. You can use the keyword SOME in place of ANY. The operator IN is equivalent to =ANY. To create the opposite search condition, use the keyword NOT or a different relational operator.

The EXISTS operator tests a subquery to see if it found any values; that is, it asks if the result of the subquery is not null.

For the complete syntax used to create a condition with a subquery, see Chapter 1 in the Informix Guide to SQL: Syntax.

Using ALL

Use the keyword ALL preceding a subquery to determine whether a comparison is true for every value returned. If the subquery returns no values, the search condition is true. (If it returns no values, the condition is true of all the zero values.)

Query 3-27 lists the following information for all orders that contain an item for which the total price is less than the total price on every item in order number 1023.

Query 3-27

SELECT  order_num,  stock_num,  manu_code,  total_price

	FROM  items

	WHERE  total_price  <  ALL

		(SELECT  total_price  FROM  items  

			WHERE  order_num  =  1023)


	Query Result 3-27

Using ANY

Use the keyword ANY (or its synonym SOME) preceding a subquery to determine whether a comparison is true for at least one of the values returned. If the subquery returns no values, the search condition is false. (Because no values exist, the condition cannot be true for one of them.)

Query 3-28 finds the order number of all orders that contain an item for which the total price is greater than the total price of any one of the items in order number 1005.

Query 3-28

SELECT  DISTINCT  order_num

	FROM  items

	WHERE  total_price  >  ANY

		(SELECT  total_price  

			FROM  items

			WHERE  order_num  =  1005)


	Query Result 3-28

Single-Valued Subqueries

You do not need to include the keyword ALL or ANY if you know the subquery can return exactly one value to the outer-level query. A subquery that returns exactly one value can be treated like a function. This kind of subquery often uses an aggregate function because aggregate functions always return single values.

Query 3-29 uses the aggregate function MAX in a subquery to find the order_num for orders that include the maximum number of volleyball nets.

Query 3-29

SELECT  order_num  FROM  items

	WHERE  stock_num  =  9  

		AND  quantity  =

			(SELECT  MAX  (quantity)  

				FROM  items

				WHERE  stock_num  =  9)


	Query Result 3-29

Query 3-30 uses the aggregate function MIN in the subquery to select items for which the total price is higher than 10 times the minimum price.

Query 3-30

SELECT  order_num,  stock_num,  manu_code,  total_price

	FROM  items  x

	WHERE  total_price  >

		(SELECT  10  *  MIN  (total_price)

			FROM  items

			WHERE  order_num  =  x.order_num) 





Query Result 3-30

Correlated Subqueries

Query 3-31 is an example of a correlated subquery, which returns a list of the 10 earliest shipping dates in the orders table. It includes an ORDER BY clause after the subquery to order the results because you cannot include ORDER BY within a subquery.

Query 3-31

SELECT  po_num,  ship_date  FROM  orders  main

	WHERE  10  >

		(SELECT  COUNT  (DISTINCT  ship_date)

			FROM  orders  sub

			WHERE  sub.ship_date  >  main.ship_date)

			AND ship_date IS NOT NULL

	ORDER  BY  ship_date,  po_num

The subquery is correlated because the number that it produces depends on main.ship_date, a value that the outer SELECT produces. Thus, the subquery must be executed anew for every row that the outer query considers.

Query 3-31 uses the COUNT function to return a value to the main query. The ORDER BY clause then orders the data. The query locates and returns the 13 rows that have the 10 latest shipping dates, as Query Result 3-31 shows.

Query Result 3-31

If you use a correlated subquery, such as Query 3-31, on a very large table, you should index the ship_date column to improve performance. Otherwise, this SELECT statement is inefficient because it executes the subquery once for every row of the table. Indexing and performance issues are discussed in the administrator's guide for your database server.

Using EXISTS

The keyword EXISTS is known as an existential qualifier because the subquery is true only if the outer SELECT, as Query 3-32a shows, finds at least one row.

Query 3-32a

SELECT  UNIQUE  manu_name,  lead_time  

	FROM  manufact

	WHERE  EXISTS

		(SELECT  *  FROM  stock  

			WHERE  description  MATCHES  '*shoe*'

				AND  manufact.manu_code  =  stock.manu_code)

You can often construct a query with EXISTS that is equivalent to one that uses IN. You can also substitute =ANY for IN, as Query 3-32b shows.

Query 3-32b

SELECT  UNIQUE  manu_name,  lead_time  

	FROM  stock,  manufact

	WHERE  manufact.manu_code  IN

		(SELECT  manu_code  FROM  stock  

			WHERE  description  MATCHES  '*shoe*')

				AND  stock.manu_code  =  manufact.manu_code

Query 3-32a and Query 3-32b return rows for the manufacturers that produce a kind of shoe as well as the lead time for ordering the product. Query Result 3-32 shows the return values.


	Query Result 3-32

You cannot use the predicate IN for a subquery that contains a column with a CLOB, BLOB, TEXT, or BYTE data type.

Add the keyword NOT to IN or to EXISTS to create a search condition that is the opposite of the one in the preceding queries. You also can substitute !=ALL for NOT IN.

Query 3-33 shows two ways to do the same thing. One way might allow the database server to do less work than the other, depending on the design of the database and the size of the tables. To find out which query might be better, use the SET EXPLAIN command to get a listing of the query plan. SET EXPLAIN is discussed in the INFORMIX-Universal Server Performance Guide and in Chapter 1 of the Informix Guide to SQL: Syntax.

Query 3-33

SELECT customer_num, company FROM customer

	WHERE customer_num NOT IN

		(SELECT customer_num FROM orders

			WHERE customer.customer_num = orders.customer_num)



SELECT customer_num, company FROM customer

	WHERE NOT EXISTS

		(SELECT * FROM orders

			WHERE customer.customer_num = orders.customer_num)

Each statement in Query 3-33 returns the rows that Query Result 3-33 shows, which identify customers who have not placed orders.


	Query Result 3-33

The keywords EXISTS and IN are used for the set operation known as intersection, and the keywords NOT EXISTS and NOT IN are used for the set operation known as difference. These concepts are discussed in "Set Operations".

Query 3-34 performs a subquery on the items table to identify all the items in the stock table that have not yet been ordered.

Query 3-34

SELECT  stock.*  FROM  stock  

	WHERE  NOT  EXISTS

		(SELECT  *  FROM  items  

			WHERE stock.stock_num = items.stock_num

				AND stock.manu_code = items.manu_code)

Query 3-34 returns the rows that Query Result 3-34 shows.


	Query Result 3-34

No logical limit exists to the number of subqueries a SELECT statement can have, but the size of any statement is physically limited when it is considered as a character string. However, this limit is probably larger than any practical statement that you are likely to compose.

Perhaps you want to check whether information has been entered correctly in the database. One way to find errors in a database is to write a query that returns output only when errors exist. A subquery of this type serves as a kind of audit query, as Query 3-35 shows.

Query 3-35

SELECT  *  FROM  items

	WHERE  total_price  !=  quantity  *

		(SELECT  unit_price  FROM  stock

			WHERE  stock.stock_num  =  items.stock_num

				AND  stock.manu_code  =  items.manu_code)

Query 3-35 returns only those rows for which the total price of an item on an order is not equal to the stock unit price times the order quantity. If no discount has been applied, such rows were probably entered incorrectly in the database. The query returns rows only when errors occur. If information is correctly inserted into the database, no rows are returned.


	Query Result 3-35