Correlated Subquery Processing

Correlated subqueries are not as neat, but they are very valuable (they can handle problems you can't easily approach with joins or simple subqueries). For now, just understand the syntax, and get an idea of how correlated subqueries work. As you experiment, you'll become comfortable with them.

In the correlated subquery, the inner query cannot be evaluated independently: It references the outer query and is executed once for each qualified row in the outer query. In the example in Figure 8.2, the outer table (publishers) has three rows, so the inner query will run three times.

Conceptually, the processing follows these steps:

1.
The outer query finds the first name in the publishers table (for example, New Age Books).

SQL
select pub_name
from publishers p
pub_name
========================================
New Age Books
Binnet & Hardley
Algodata Infosystems
[3 rows]

2.
The inner query joins the associated publishers.pub_id (0736) to titles.pub_id to find qualifying rows in the titles table (it finds six). Only one of the six is a business book. (The relevant columns follow.)

SQL
select *
from titles
where pub_id = '0736'
   and type =  'business'

title_id ... type   ... pub_id
============ ========== ==========
BU2075       business   0736
[1 row]

3.
The inner query passes this information back to the outer: New Age Books qualifies.

4.
The outer query then goes to work again, this time passing the pub_id of the second row in publishers (Binnet & Hardley, 0877 ) to the inner query.

5.
Using this value, the inner query finds no rows with the correct type and signals “no rows found” to the outer query. Binnet & Hardley does not qualify.

6.
The subquery runs the third time with a publishers.pub_id of 1389 (Algodata Infosystems) and finds six more rows. Three of them have type “business.” Algodata Infosystems will be in the final result set.

Qualifying Column Names

Correlated queries require explicit naming for columns from the outer query (you can use aliases, as p.pub_id for publishers.pub_id in Figure 8.2). Columns belonging to the inner query table are implicitly qualified by it.

However, you can always specify both tables:

SQL
select pub_name
from publishers
where exists
   (select *
    from titles
    where titles.pub_id = publishers.pub_id and
       type =  'business' )

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.145.81.24