Sub Queries Nested Queries in SQL

1
Sub Queries(Nested Queries)in SQL
2
More Complex Queries
Consider the following problem
List the description and price for products whose
price is above average, that is, more than the
average price of all products.

• Proposed Solution
• SELECT DESCRIP, PRICE
• FROM PRODUCT
• WHERE PRICE gt AVG(PRICE)

3
Invalid SQL Syntax!!

• We get the following error message

SQLCODE -120, ERROR A WHERE CLAUSE OR SET
CLAUSE INCLUDES A COLUMN FUNCTION

• SQL does not allow us to include a column
  function (like AVG, SUM, MAX, MIN) in the WHERE
  clause.
• The WHERE clause condition must be answerable on
  the basis of a single row of the PRODUCT table
  cited in the FROM clause

4
Two Step Solution

• We could do this in 2 steps
• Step 1 Find the average
• SELECT AVG( PRICE)
• FROM PRODUCT
• The result

------------------ 1128.6250000000
5
Two Step Solution

• Step 2 Use answer to Step 1 in another query
• SELECT DESCRIP, PRICE
• FROM PRODUCT
• WHERE PRICE gt 1228.625

DESCRIP PRICE ---------------
---------- STILL VIDEO CAM 1500.00 486
DX/33 1400.00 LASER PRINTER
3500.00
6
Limitation of This Solution

• Each time we want this question answered
• We run first query again
• Transfer answer to second query
• Run modified second query
• We would like to automatically transfer the
  answer from first query into the second query.

7
Nested Query Solution

• Replace the constant value (the average) given in
  the second query with the first query (which
  gives this value)
• SELECT DESCRIP, PRICE
• FROM PRODUCT
• WHERE PRICE gt
• (SELECT AVG(PRICE)
• FROM PRODUCT)
• Called a Nested Query or a Subquery

Outer query
Inner query
8
Sub-Query Yielding Multiple Values

• Problem List the names and phones of
  customers who placed orders thru sales rep with
  id JMH during the month of January 2000.
• Step 1 Find orders which meet the desired
  criteria. List their account numbers
• SELECT ACCTNO
• FROM ORDER
• WHERE ORDER_DATE
• BETWEEN 01/01/2001 AND 01/31/2001
• AND ORDREP JMH

9
Sub-Query Yielding Multiple Values

• This gives us a list of account numbers, e.g.
• 1111, 1456, 2345, ? ,6789, 7012
• Step 2 Use this list to find the account names
  and phones
• SELECT ACCTNAME, PHONE
• FROM ACCOUNT
• WHERE ACCTNO IN (1111,1456, 2345,?,6789, 7012)

10
Subquery Version

• SELECT ACCTNAME, PHONE
• FROM ACCOUNT
• WHERE ACCTNO IN
• (SELECT ACCTNO
• FROM ORDER
• WHERE ORDER_DATE
• BETWEEN 01/01/2001 AND 01/31/2001
• AND ORDREP JMH)
• Note the data to be retrieved only comes from the
  table named in the outer query

1st Query replaces list
11
When to use Subquery?

• If answer data is from one table, but criteria
  from different table (but related), then subquery
  may be valid approach
• In our example
• Subquery is on ORDER table
• Criteria on this table
• Outer query on ACCOUNT table
• Only need output from this table

12
JOIN Alternative to Subquery

• This problem could also be done with a JOIN
• Would HAVE to use JOIN if wanted Orderno or
  Orderdate, for example, in answer

• SELECT ACCTNAME, PHONE
• FROM ACCOUNT A
• INNER JOIN ORDER O
• ON O.ACCTNO A.ACCTNO
• WHERE ORDER_DATE
• BETWEEN 01/01/2001 AND 01/31/2001
• AND ORDREP JMH)

13
Sub-Query for Matching rows

• List product numbers, descriptions for products
  that have been ordered
• Subquery method
• SELECT PRODNO, DESCRIP
• FROM PRODUCT
• WHERE PRODNO IN
• (SELECT PRODNO
• FROM ORDERLINE)
• Could be done with JOIN, also

14
Sub-Query for Non-matching Rows

• List product numbers, descriptions for products
  that have NOT been ordered
• SELECT PRODNO, DESCRIP
• FROM PRODUCT
• WHERE PRODNO NOT IN
• (SELECT PRODNO
• FROM ORDERLINE)
• Cant be done
• with JOIN

PRODNO DESCRIP ------
--------------- 3276 LASER PRINTER
15
Incorrect Solution

• The following is NOT a solution to this problem
• SELECT PRODNO, DESCRIP
• FROM PRODUCT P, ORDERLINE O
• WHERE NOT P.PRODNO O.PRODNO
• This produces
• Pairs of orderline rows with every product
  EXCEPT the one in the orderline

16
Details that go with Maximums

• Problem list the product numbers, descriptions
  of the highest-priced product
• INCORRECT SOLUTION
• SELECT PRODNO, DESCRIP, MAX(PRICE)
• FROM PRODUCT
• SQL Rules cannot have MAX, SUM, etc on same
  SELECT as non-summarized field names

17
Correct Solution

• SELECT PRODNO, DESCRIP, PRICE
• FROM PRODUCT
• WHERE PRICE IN
• (SELECT MAX(PRICE)
• FROM PRODUCT)
• Inner query gets maximum
• Outer query gets detail row data that matches

Could also use
18
Multiple Levels of Nesting in Queries

• Find all orders placed for items with above
  average prices
• Step 1 Find the average price
• SELECT AVG(PRICE)
• FROM PRODUCT
• Step 2 Find items with above average prices
• SELECT PRODNO
• FROM PRODUCT
• WHERE PRICE gt (average price)

19
Building on Earlier Steps

• Step 3 Find order numbers which include these
  items
• SELECT ORDERNO
• FROM ORDERLINE
• WHERE PRODNO IN (list of product numbers)
• Step 4 Find orders which include such items
• SELECT ORDERNO, ORDERDATE
• FROM ORDER
• WHERE ORDERNO IN (list of order numbers)

20
Multiple Levels of Nesting

• SELECT ORDERNO, ORDERDATE
• FROM ORDER
• WHERE ORDERNO IN
• (SELECT ORDERNO
• FROM ORDERLINE
• WHERE PRODNO IN
• (SELECT PRODNO
• FROM PRODUCT
• WHERE PRICE gt
• (SELECT AVG(PRICE)
• FROM PRODUCT)))

21
Alternate Solution Joins and Subquery

• This problem can also be solved using combination
  of joins and subqueries For example
• SELECT ORDERNO, ORDERDATE
• FROM ORDER R INNER JOIN ORDERLINE L
• ON R.ORDERNO L.ORDERNO
• INNER JOIN PRODUCT P
• ON L.PRODNO P.PRODNO
• WHERE PRICE gt
• (SELECT AVG(PRICE)
• FROM PRODUCT))

22
Finding the Last 10 Orders

• We have seen how to get orders from last four
  dates, but how to get the last 10 orders (for
  example, if 4 on last date, just want those)
• SELECT O1.ORDERNO, O1.ORDERDATE
• FROM ORDER O1
• WHERE 10 gt
• (SELECT COUNT()
• FROM ORDER O2
• WHERE O1.ORDERDATE gt O2.ORDERDATE
• This will always include last 10 and ties

23
Subquery in SELECT clause

• SELECT P.PROJNO, (SELECT SUM(HRS) FROM TASK T
  WHERE T.PROJNO P.PROJNO) TOTAL_HRS
• FROM PROJECT P

24
Correlated Subqueries
25
Correlated Subqueries

• Sometimes, need to restrict subquery to rows that
  match each row of outer query
• Find all products whose price exceeds their
  vendors average price.
• SELECT PRODNO, DESCRIP, VENDCODE
• FROM PRODUCT P1
• WHERE PRICE gt
• (SELECT AVG(PRICE)
• FROM PRODUCT P2
• WHERE P1.VENDCODE P2.VENDCODE)
• Here the inner query is evaluated separately for
  each row of the outer query table.

26
Revisiting a Previous Problem

• Find all products that have had orders placed.
• Solution 1 Using JOIN
• SELECT DISTINCT PRODNO, DESCRIP,
• VENDCODE
• FROM PRODUCT P INNER JOIN ORDERLINE
  L
• ON P.PRODNO L.PRODNO
• Retrieves many rows (thousands?) for each product

27
Previous Problem

• Solution 2 Using IN
• SELECT PRODNO, DESCRIP, VENDCODE
• FROM PRODUCT P
• WHERE PRODNO IN
• (SELECT PRODNO FROM ORDERLINE)

28
Correlated Subquery with EXISTS

• Solution 3, a new alternative
• SELECT PRODNO, DESCRIP, VENDCODE
• FROM PRODUCT P
• WHERE EXISTS
• (SELECT FROM ORDERLINE L
• WHERE P.PRODNO L.PRODNO)
• Might be more efficient to execute than join
  query, maybe even the IN query. Inner query
  stops when the first matching row is found.

29
Using NOT EXISTS

• Find all sales reps that do not have any orders
  placed.
• SELECT FNAME, LNAME
• FROM SALESREP S
• WHERE NOT EXISTS
• (SELECT
• FROM ORDER
• WHERE ORDREP S.REPID)
• This is an alternative to subquery using NOT IN
• NOT EXISTS, EXISTS may be more efficient

30
NOT EXISTS for finding items at maximum
Find the most recent order for each account

• SELECT ACCTNO, ORDERNO, ORDERDATE
• FROM ORDER A
• WHERE NOT EXISTS
• (SELECT
• FROM ORDER B
• WHERE A.ACCTNO B.ACCTNO
• AND A.ORDERDATE lt B.ORDERDATE)

• If multiple orders on most recent date (for a
  given account) number, returns all of them