Subqueries

1
Subqueries

• CIT 381

2
Nesting

• A query can be put inside another query
• Most commonly in the WHERE clause
• Sometimes in the FROM clause (depending on the
  software)
• This subquery is executed first (if possible)

3
Find the average sales of the Sales
Reps SELECT AVG(sales) FROM salesreps
Find those Sales Reps whose sales exceed the
average SELECT name, sales FROM salesreps WHERE
sales gt (SELECT AVG(sales) FROM salesreps)
4
Find the Sales Rep with maximum sales Two
ways SELECT name, sales FROM salesreps WHERE
sales (SELECT MAX(sales) FROM
salesreps) -or- SELECT name, sales FROM
salesreps WHERE sales gt ALL (SELECT
sales FROM salesreps)
5
Correlated vs Uncorrelated

• The previous subqueries did not depend on
  anything outside the subquery
• and thus need to be executed just once.
• There are called uncorrelated.
• A correlated subquery depends on data from the
  outer query
• and thus has to be executed for each row of the
  outer table(s)

6
Correlated Example
For each office, find the Sales Rep with
the most sales in that office. SELECT
s_out.name, s_out.sales FROM salesreps
s_out WHERE s_out.sales (SELECT
MAX(s_in.sales) FROM salesreps s_in WHERE
s_in.rep_offices_out.rep_office)
7
Macho SQL
SELECT s1.rep_office, s1.name, s1.sales FROM
salesreps AS s1, (SELECT rep_office, MAX(sales)
AS ms FROM salesreps GROUP BY rep_office) AS
s2 WHERE s1.saless2.ms AND s1.rep_offices2.rep_o
ffice
The subquery could be viewed as a table and put
in the FROM clause.
8
In the previous lecture we wrote a query for
Get the average and total sales of those Reps
with over 30k in total sales. Someone asked
How do we get the sum of those total sales?
That is, sum the sales of all those who exceed
30,000 in sales. We can (1) use a subquery in
the FROM clause Or (2) use a TEMP table.
(Solution 2 not in Access??)
9
Solution 1
SELECT SUM(TotalSales) FROM (SELECT s.name,
AVG(o.amount) as AvgSales,
SUM(o.amount) AS TotalSales FROM salesreps
s, orders o WHERE s.empl_numo.rep
GROUP BY s.name HAVING SUM(o.amount)gt30000)
10
Subqueries as Sets

• A subquery can be viewed as a set (in the WHERE
  clause)
• Viewed as a table in the FROM clause (more rare
  and less supported)
• As sets, can use set operations
• IN, EXISTS, ANY, ALL

11
Simulate a Join
Find all managers. SELECT name FROM
salesreps WHERE empl_num IN (SELECT
manager FROM salesreps) You could do this as
a self-join. That would be more efficient,
because join computations are optimized.
12
Tricky NOT IN
Find those people who are not managers. SELECT
name FROM salesreps WHERE empl_num NOT IN (SELECT
manager FROM salesreps) OOPS (try
it). This gives no names. Obviously the wrong
answer.
13
Look at contents of SALESREPS
14
Tricky NULLS again
One of the managers is NULL. That could be
anybody (so we cant say anyone is not in
there). SELECT name FROM salesreps WHERE
empl_num NOT IN (SELECT manager FROM
salesreps WHERE manager IS NOT NULL)