Informix Guide to SQL: Tutorial Composing Advanced SELECT Statements

The following situations define the types of subqueries the database server supports:

• A SELECT statement nested in the SELECT list of another SELECT statement
• a SELECT statement nested in the WHERE clause of another SELECT statement (or in an INSERT, DELETE, or UPDATE statement)

Each subquery must contain a SELECT clause and a FROM clause. Subqueries can be correlated or uncorrelated. A subquery (or inner SELECT statement) is correlated when the value 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, as follows:

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

A subquery can occur in the select list of another SELECT statement. Query 5-20 shows how you might use a subquery in a select list to return the total shipping charges (from the orders table) for each customer in the customer table. You could also write this query as a join between two tables.

SELECT customer.customer_num,
	(SELECT SUM(ship_charge) 
	 	FROM orders
	 	WHERE customer.customer_num = orders.customer_num) 
			AS total_ship_chg
	FROM customer 

 






 Query Result 5-20 

This section describes subqueries that occur as a SELECT statement nested in the WHERE clause of another SELECT statement.

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. You cannot use the EXISTS keyword in a subquery that contains a column with a TEXT or BYTE data type.

For the syntax that you use to create a condition with a subquery, see the Informix Guide to SQL: Syntax.

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

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 5-21 







 

Use the keyword ANY (or its synonym SOME) before 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 5-22 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.

SELECT  DISTINCT  order_num

	FROM  items

	WHERE  total_price  >  ANY

		(SELECT  total_price  

			FROM  items

			WHERE  order_num  =  1005) 

 






 Query Result 5-22 







 

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 5-23 uses the aggregate function MAX in a subquery to find the order_num for orders that include the maximum number of volleyball nets.

SELECT  order_num  FROM  items

	WHERE  stock_num  =  9  

		AND  quantity  =

			(SELECT  MAX  (quantity)  

				FROM  items

				WHERE  stock_num  =  9)


 






 Query Result 5-23 







 

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

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 5-24 







 

Query 5-25 is an example of a correlated subquery that returns a list of the 10 latest 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.

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 re-executed for every row that the outer query considers.

Query 5-25 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 16 rows that have the 10 latest shipping dates, as Query Result 5-25 shows.

Query Result 5-25

If you use a correlated subquery, such as Query 5-25, on a 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. For information about indexing and performance issues, see the Administrator's Guide and your Performance Guide.

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

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. Query 5-26b uses an IN predicate to construct a query that returns the same result as Query 5-26a.

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 5-26a and Query 5-26b return rows for the manufacturers that produce a kind of shoe as well as the lead time for ordering the product. Query Result 5-26 shows the return values.

Query Result 5-26

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

Query 5-27 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 your Performance Guide and the Informix Guide to SQL: Syntax.

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 5-27 returns the rows that Query Result 5-27 shows, which identify customers who have not placed orders.

Query Result 5-27

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 5-28 performs a subquery on the items table to identify all the items in the stock table that have not yet been ordered.

SELECT  *  FROM  stock  

	WHERE  NOT  EXISTS

		(SELECT  *  FROM  items  

			WHERE stock.stock_num = items.stock_num

				AND stock.manu_code = items.manu_code)

Query 5-28 returns the rows that Query Result 5-28 shows.

Query Result 5-28

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 5-29 shows.

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 5-29 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 5-29