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:
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 IBM 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.)
Figure 302 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)
order_num stock_num manu_code total_price
1003 9 ANZ $20.00
1005 6 SMT $36.00
1006 6 SMT $36.00
1010 6 SMT $36.00
1013 5 ANZ $19.80
1013 6 SMT $36.00
1018 302 KAR $15.00Use 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.)
Figure 304 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)order_num
1001
1002
1003
1004
·
·
·
1020
1021
1022
1023You 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.
Figure 306 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)
order_num
1012Figure 308 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)
order_num stock_num manu_code total_price
1003 8 ANZ $840.00
1018 307 PRC $500.00
1018 110 PRC $236.00
1018 304 HRO $280.00Figure 310 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_numThe 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.
Figure 310 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 Figure 311 shows.
po_num ship_date 4745 06/21/1998 278701 06/29/1998 429Q 06/29/1998 8052 07/03/1998 B77897 07/03/1998 LZ230 07/06/1998 B77930 07/10/1998 PC6782 07/12/1998 DM354331 07/13/1998 S22942 07/13/1998 MA003 07/16/1998 W2286 07/16/1998 Z55709 07/16/1998 C3288 07/25/1998 KF2961 07/30/1998 W9925 07/30/1998
If you use a correlated subquery, such as Figure 310, 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 IBM Informix: Administrator's Guide and your IBM Informix: Performance Guide.
The keyword EXISTS is known as an existential qualifier because the subquery is true only if the outer SELECT, as Figure 312 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. Figure 313 uses an IN predicate to construct a query that returns the same result as Figure 312.
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_codeFigure 312 and Figure 313 return rows for the manufacturers that produce a kind of shoe, as well as the lead time for ordering the product. Figure 314 shows the return values.
manu_name lead_time Anza 5 Hero 4 Karsten 21 Nikolus 8 ProCycle 9 Shimara 30
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.
Figure 315 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 IBM Informix: Performance Guide and IBM 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 Figure 315 returns the rows that Figure 316 shows, which identify customers who have not placed orders.
customer_num company
102 Sports Spot
103 Phil's Sports
105 Los Altos Sports
107 Athletic Supplies
108 Quinn's Sports
109 Sport Stuff
113 Sportstown
114 Sporting Place
118 Blue Ribbon Sports
125 Total Fitness Sports
128 Phoenix UniversityThe 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.
Figure 317 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)Figure 317 returns the rows that Figure 318 shows.
stock_num manu_code description unit_price unit unit_descr
101 PRC bicycle tires $88.00 box 4/box
102 SHM bicycle brakes $220.00 case 4 sets/case
102 PRC bicycle brakes $480.00 case 4 sets/case
105 PRC bicycle wheels $53.00 pair pair
·
·
·
312 HRO racer goggles $72.00 box 12/box
313 SHM swim cap $72.00 box 12/box
313 ANZ swim cap $60.00 box 12/boxNo 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 typically larger than most practical statements 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 Figure 319 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)Figure 319 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.
item_num order_num stock_num manu_code quantity total_price
1 1004 1 HRO 1 $960.00
2 1006 5 NRG 5 $190.00