Using Subqueries in DB2

1
Using Subqueries in DB2

• Pam Odden

2
Objectives

• What is a subquery?
• How to code a subquery
• Correlated vs non-correlated subqueries
• Using subqueries when updating
• Subqueries as nested table views

3
What is a Subquery?

• A subquery is a query-within-a-query. The
  results of the subquery are used by DB2 to
  determine the results of the higher-level query.
• The subquery is enclosed in parentheses, but
  otherwise it is in the same form as a regular
  SELECT statement.
• Subqueries provide an efficient way to handle
  query requests that are expressed in terms of the
  results of other queries.
• Example
• List history information for non-obsolete items
  for a delivery location
• SELECT STOCK_NUMBER, CHARGE_TO_ACCOUNT,
  REQ_DATE
• FROM MSTORDB1.STOR_HISTORY
  
• WHERE DELIVERY_LOCATION '058'
  
• AND STOCK_NUMBER NOT IN
  
• (SELECT STOCK_NUMBER FROM MSTORDB1.OBSOLETE_ITE
  MS)

4
How to Code a Subquery

• A subquery is usually part of a predicate, which
  can be part of a WHERE or HAVING clause
• The predicate is in the form
• Operand operator (subquery)
• STOCK_NUMBER NOT IN
  (SELECT STOCK_NUMBER FROM MSTORDB1.OBSOLETE_ITEMS)
  
• VENDOR_PRICE (SELECT MAX(VENDOR_PRICE) FROM
  VENDOR_ITEMS
• A predicate containing a subquery can be preceded
  by the word NOT, and can be linked to other
  predicates by the words AND and OR.
• WHERE X IN (subquery1) AND (Y gt SOME (subquery2)
  OR Z IS NULL)
• Subqueries can appear in the predicates of other
  subqueries, as nested subqueries at some level of
  nesting. DB2 allows nesting down to a level of
  15, but it is rarely required.
• The ORDER BY clause cannot be specified in a
  subquery. Since the results are used internally
  and never seen by the user, sorting is not
  necessary anyway.
• A subquery cannot be the UNION of more than one
  SELECT statement.

5
Subquery Search Conditions

• There are a number of ways a subquery can be
  specified in either a WHERE or HAVING clause
• Basic comparison test
• Using the IN keyword
• Quantified Predicates ALL, ANY, and SOME
• Using the EXISTS keyword

6
Predicates Using Comparison Test

• Compares the value of an expression to a single
  value produced by a subquery using comparison
  operators , ltgt, lt, gt, lt, gt
• The results must be a single value of the
  appropriate data type. If more than one row is
  returned, SQL reports an error.
• If the subquery produces no rows, or a NULL
  value, the comparison test returns NULL.
• Select items backordered longer than 3 months
• SELECT STOCK_NUMBER, BACKORD_DATE
• FROM MSTORDB1.BACK_ORDERS
• WHERE BACKORD_DATE lt
• (SELECT CURRENT DATE - 3 MONTHS
• FROM SYSIBM.SYSDUMMY1)
• STOCK_NUMBER BACKORD_DATE
• ----------------------------------------
• 9998507970 09/11/2002
• 9998507970 09/06/2002
• Select open requisitions with quantity greater
  than the average
• SELECT STOCK_NUMBER, QUANTITY, REQ_DATE
  
• FROM MSTORDB1.REQ_OPEN
  
• WHERE QUANTITY gt
  

7
Predicates Using IN Test

• Compares a single operand to a column of data
  values produced by a subquery
• Returns true if the operand matches one of the
  values in the column, false otherwise.
• Works just like the simple IN test, except that
  the set of values is produced by a subquery
  instead of being explicitly listed in the
  statement.
• Select information for all items requisitioned on
  a particular day
• SELECT VENDOR_ITEM, VENDOR_DESC, VENDOR_PRICE
  
• FROM MSTORDB1.VENDOR_ITEMS
  
• WHERE VENDOR_ITEM IN
  
• (SELECT VENDOR_ITEM
  
• FROM MSTORDB1.VENDOR_HISTORY
  
• WHERE REQ_DATE '02/04/2002')
  
• ---------------------------------------------
  -------------------------
• VENDOR_ITEM VENDOR_DESC
  VENDOR_PRICE
• ---------------------------------------------
  -------------------------
• AAGE71750
  CAL,RFL,DESK,3.5"X6" .88
• AAG7026005
  BOOK,APPT,MO,11"X9",BK 13.53
• AA0200 MOP. BOWL
  .38
• ACC12992
  FASTENER,2PC,2"CAP,50BX 4.13

8
Predicates Using ALL, ANY or SOME

• Keywords ALL, ANY or SOME can be used to compare
  a single operand to a column of values produced
  by a subquery.
• If ANY or SOME is used, the predicate returns
  true if any of the individual comparisons yields
  a true result. (When used with , equivalent to
  using IN.)
• If ALL is used, all individual comparisons must
  evaluate to true in order for the predicate to
  return true.
• Select backorders with a quantity greater than
  all of the outstanding requisitions for the item
• SELECT STOCK_NUMBER, QUANTITY
• FROM MSTORDB1.BACK_ORDERS B
• WHERE QUANTITY gt ALL
• (SELECT QUANTITY FROM MSTORDB1.REQ_OPEN R
• WHERE R.STOCK_NUMBER B.STOCK_NUMBER)
• -------------------------------------------
• STOCK_NUMBER QUANTITY
• -------------------------------------------
• 9998500559 1
• 9998500546 4

9
Predicates Using Exists

• The Exists test checks whether a subquery
  produces any rows of query results.
• If the subquery returns any rows, the exists test
  is true. If the subquery returns no rows, the
  test is false. Exists never returns a NULL
  value.
• You can reverse the logic by using NOT EXISTS.
• Select inventory information for items having
  open requisitions
• SELECT STOCK_NUMBER, STOCK_CYCLE, STOCK_MONTH,
  STOCK_BACKORD
• FROM MSTORDB1.INV_CONTROL I
  
• WHERE EXISTS
  
• (SELECT STOCK_NUMBER
  
• FROM MSTORDB1.REQ_OPEN
  
• WHERE STOCK_NUMBER I.STOCK_NUMBER)
  
• ---------------------------------------------
  -------------------
• STOCK_NUMBER STOCK_CYCLE
  STOCK_MONTH STOCK_BACKORD
• ---------------------------------------------
  -------------------
• 0151520409030 A
  0
• 0151530773515 A
  0
• 0151530773530 B
  0

10
Using Correlation Names in References

• A correlation name is a short name associated
  with a table in a FROM clause, and used with
  column names to identify which tables column
  youre referring to.
• The correlation name allows subqueries to refer
  back to tables at any level of nesting.
• Regardless of the level of nesting, an outer
  reference always takes on the value of the column
  in the current row of the outer table.
• Select inventory info for non-obsolete items
  that have been delivered to a particular user
• SELECT STOCK_NUMBER, STOCK_CYCLE, STOCK_MONTH,
  STOCK_BACKORD
• FROM MSTORDB1.INV_CONTROL I
  
• WHERE STOCK_NUMBER IN
  
• (SELECT STOCK_NUMBER
  
• FROM MSTORDB1.STOR_HISTORY R
  
• WHERE DELIVERY_LOCATION
  
• (SELECT DISTINCT(DELIVERY_LOCATION)
  
• FROM MSTORDB1.ORDER_CONTROL
  
• WHERE USER_CODE 'AAH'))
  
• AND NOT EXISTS
  
• (SELECT STOCK_NUMBER
  
• FROM MSTORDB1.OBSOLETE_ITEMS O
  
• WHERE O.STOCK_NUMBER
  I.STOCK_NUMBER)
• STOCK_NUMBER STOCK_CYCLE
  STOCK_MONTH STOCK_BACKORD
• ---------------------------------------------
  --------------------

11
Subqueries in the HAVING Clause

• When a subquery appears in the HAVING clause, it
  works as part of the selection process to
  determine which of the rows grouped by the GROUP
  BY clause to return.
• Select the item types whose highest price is
  greater than the average price for all items.
• SELECT ITEM_TYPE, MAX(VENDOR_PRICE)
  
• FROM MSTORDB1.VENDOR_ITEMS
  
• GROUP BY ITEM_TYPE
  
• HAVING MAX(VENDOR_PRICE) gt
  
• (SELECT AVG(VENDOR_PRICE) FROM
  MSTORDB1.VENDOR_ITEMS)
• ---------------------------------------------
  ----------
• ITEM_TYPE
  
• ---------------------------------------------
  ----------
• 1 15314.69
  
• 2 1046.13
  
• 4 2477.00
• When a correlated subquery is used in the HAVING
  clause, the outer reference must have only one
  value per row group. This limits the outer
  reference to either a gouping column or a column
  function.

12
Correlated Subqueries in Update and Delete
Statements

• When a subquery is used in an UPDATE, INSERT or
  DELETE statement, it cannot reference the same
  table whose rows you are updating, inserting or
  deleting.
• The results must not depend on the order in which
  DB2 accesses the rows.
• If a DELETE statement has a subquery that
  references a table involved in the deletion, the
  last delete rule in the path to that table must
  be RESTRICT or NO ACTION.
• 
  

13
Subqueries as Nested Table Expressions

• A subquery may be used as a complex operand in
  the FROM clause.
• This is called a nested table expression.
• Enclose the expression in parentheses.
• Give the expression a correlation name. The
  correlation name can be used to qualify selected
  column names, however it cannot be used inside
  the same FROM clause that defines it.
• Provide unique names for the columns of the
  result table that you reference.

14
Examples of Nested Table Expressions

• Select vendor info for items costing less than
  one dollar
• SELECT CHEAP_STUFF.VENDOR_ITEM,
  CHEAP_STUFF.VENDOR_PRICE
• FROM (SELECT VENDOR_ITEM, VENDOR_PRICE
  
• FROM MSTORDB1.VENDOR_ITEMS
  
• WHERE VENDOR_PRICE lt 1.00) AS
  CHEAP_STUFF
• ---------------------------------------------
  ---------
• VENDOR_ITEM VENDOR_PRICE
  
• ---------------------------------------------
  ---------
• 1001603120 .41
• 1001603340 .57
• 1002604141 .83
• 1002604120 .28
• 1002604341 .55
• Select order authorization info where the
  updating and authorizing person are the same
• SELECT ORDER_DATE, ORDER_NAME, AUTHID, BUDGID
  
• FROM MSTORDB1.ORDER_CONTROL INNER JOIN
  
• (SELECT A.USER_ID AS AUTHID, A.AUTH_LEVEL,
  
• B.USER_ID AS BUDGID, B.BUDGET_UNIT
  

15
Summary

• A subquery is a query-within-a-query.
  Subqueries appear within one of the search
  conditions in the WHERE or HAVING clause, or as a
  nested table expression in the FROM clause.
• In the WHERE clause, the results of the subquery
  are used to select the individual rows that
  appear in the query results.
• In the HAVING clause, the results of the subquery
  are used to select the row groups that appear in
  the query results.
• Subqueries can be nested within other subqueries.
• A subquery may include an outer reference to a
  table in any of the queries that contain it,
  linking the subquery to the current row of that
  query.