Sub Queries Nested Queries in SQL - PowerPoint PPT Presentation

1 / 30

About This Presentation

Title:

Sub Queries Nested Queries in SQL

Description:

... Non-matching ... have MAX, SUM, etc on same SELECT as non-summarized field names ... Inner query stops when the first matching row is found. Slide 29. ACS ... – PowerPoint PPT presentation

Number of Views:2288

Avg rating:3.0/5.0

Slides: 31

Provided by: applied6

Category:

more less

Transcript and Presenter's Notes

Title: 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