CHAPTER 7 Subqueries

 

 

CHAPTER 7 Subqueries

 

 

1.    Chapter Summary:

1)      A subquery is a comlete query nested in the SELECT, FROM, or WHERE clause of another query. The subquery must be enclosed in parentheses and have a SELECT and a FROM caluse, at a minimum.

2)      Subqueries are completed first. The result of the subquery is used as input for the outer query.

3)      A single-row subquery can return a maximum of one value.

4)      Multiple-row subqueries return more than one row of results.

5)      Operators that can be used with multiple-row subqueries include IN, ALL, ANY, and EXISTS.

6)      Multiple-column subqueries return more than one column to the outer query. The columns of data are passed back to the outer query in the same order in which thay are listed in the SELECT clause of the subquery.

7)      NULL values returned by a multiple-row or multiple-column subquery will not present a problem if the IN or =ANY operator is used. The NVL function can be used to substitute a values for a NULL value when working with subqueries.

8)      Correlated subqueries reference a column contained in the outer query. When using correlated subqueries, the subquery is executed once for each row processed by the outer query.

9)      Subqueries can be nested to a maximum depth of 255 subqueries in the WHERE clause of the parent query. The depth is unlimited for subqueries nested in the FROM clause of the parent query.

10) With nested subqueries, the innermost subquery is executed first, then the next highest level subquery is executed, and so on, untill the outermost query is reached.

 

 

 

2.    Syntax Summary:

The following tables present a summary of the syntax that you have learned in this chapter. You can use the tables as a study guide and reference.

Syntax Guide
Subquery Types
Subqery Processing	Example
Correlated Suquery: References a column in the outer query. Executes the subquery once for every row in the outer query.	select title from books b where b.isbn IN       (select isbn        from orderitems o        where b.isbn = o.isbn);
Uncorrelated Subquery: Executes the subquery first and passed the value to the outer query.	select title from books b, orderitems o where b.isbn IN                (select isbn                 from orderitems) and b.isbn = o.isbn;
Multiple-Row comparison Operators
Operator	Description
>ALL	More than the highest value return by the subquery.
<ALL	Less than the lowest value return by the subquery.
<ANY	Less than the highest value return by the subquery.
>ANY	More than the lowest value return by the subquery.
=ANY	Equal to any value return by the subquery(same as IN).
[not]EXISTS	Row must match a value in the subquery.

 

 

3.    Single-Row, Multiple-Row and Multiple-Column subqueries Summary:

1)  Single-Row Subquery:

A single-row subquery can return to the outer query only one row of results that consists of only one column. Therefore, this text will refer to the output of a single-row subquery as a single value.

a)    Single-Row Subquery in a WHERE Clause:

--Example1:
select title, cost
from books
where cost >
             (select cost
              from books
              where title = 'DATABASE IMPLEMENTATION')
       and category = 'COMPUTER';

--Example2:
select title
from books
where retail =
            (select max(retail)
             from books);

--Example3:
select isbn, title
from books
where pubid =
           (select pubid
            from books
            where title = 'BIG BEAR AND LITTLE DOVE')
AND retail - cost >
           (select avg(retail - cost)
            from books);

b)    Single-Row Subquery in a HAVING Clause:

--Example1:
select category, avg(retail - cost) "Average Profit"
from books
group by category
HAVING avg(retail - cost) >
                         (select avg(retail - cost)
                          from books
                          where category = 'LITERATURE');

c)    Single-Row Subquery in a SELECT Clause:

--Example1:
select title, retail,
                     (select avg(retail)
                      from books) "Overall Average"
from books;

2)  Multiple-Row Subqueries:

Multiple-row subqueries are nested queries that can return more than noe row of results to the parent query. Multiple-row subqueries are commonly used in WHERE and HAVING clauses. The main rule to keep in mind when working with multiple-row subqueries is that you must use multiple-row operators.

a)    IN Operator:

--Example:
select title, retail, category
from books
where retail IN
               (select max(retail)
                from books
                group by category);

b)    ALL and ANY Operators:

-- >ALL operator:
select title, retail
from books
where retail > ALL
                  (select retail
                   from books
                   where category = 'COOKING');

-- <ALL Operator:
select title, retail
 from books
 where retail < ALL
                   (select retail
                    from books
                    where category = 'COOKING');

-- <ANY Operator:
select title, retail
from books
where retail < ANY
                   (select retail
                    from books
                    where category = 'COOKING');


-- >ANY Operator:
select title, retail
from books
where retail > ANY
                  (select retail
                   from books
                   where category = 'COOKING');


-- =ANY Operator:
select distinct title
from books natural join orderitems natural join orders
where order# = ANY
                  (select order#
                   from orderitems
                   where isbn = '0401140733')
and isbn <> '0401140733';

c)    EXISTS Operator:

-- Exists Operator:
select title
from books
 where exists
             (select isbn
              from orderitems
              where books.isbn = orderitems.isbn);

-- NOT Exists Operator:
select title
from books
where NOT EXISTS
                (select isbn
                 from orderitems
                 where books.isbn = orderitems.isbn);

 

 

d)    Multiple-Row Subquery in a HAVING Clause:

--
select order#, sum(retail*quantity)
from orders natural join orderitems natural join books
HAVING sum(retail * quantity) > any
                                    (select avg(sum(retail * quantity))
                                     from orders natural join orderitems natural join books
                                     group by shipstate)
group by order#;

3)  Multiple-Column Subquery:

A multipel-column subquery returns more than one column to the outer query. A multiple-column subquery can be listed in the FROM, WHERE, or HAVING clause of a query. If the multiple-column subquery is include in the FROM clause of the outer query, the subquery actually generates a temporary table that can be be referenced by other clause of the outer query.

a)    Multiple-Column Subquery in a FROM Clause:

--
select b.title, b.retail, a.category, a.cataverage
from books b,
             (select category, avg(retail) cataverage
              from books
              group by category) a
where b.category = a.category
and b.retail > a.cataverage;

--
select title, retail, category, cataverage
from books natural join
                        (select category, avg(retail) cataverage
                         from books
                         group by category)
where retail > cataverage;

b)    Multiple-Column Subquery in a WHERE Clause:

--
select title, retail, category
from books
where (category, retail) IN
                           (select category, max(retail)
                            from books
                            group by category)
order by category;