| Team Fly | |
A correlated subquery is a query that references a column from the main query. In our example that follows, it enables us to first retrieve the average list price for each product category and then join it back (correlate it) to the product category in the outer query. Let's take a look at the example and its output:
SQL> select substr(prod_category,1,22) Category, 2 substr(prod_name,1,39) Product, 3 prod_list_price List 4 from products p 5 where prod_list_price > (select avg(prod_list_price) 6 from products 7 where p.prod_category = prod_category) 8 order by substr(prod_category,1,22), prod_list_price desc; CATEGORY PRODUCT LIST ---------------------- --------------------------------------- -------- Electronics Home Theatre Package with DVD-Audio/Vid 599.99 Electronics 8.3 Minitower Speaker 499.99 Electronics Y Box 299.99 Hardware Envoy Ambassador 1299.99 Peripherals and Access 17'' LCD w/built-in HDTV Tuner 999.99 Peripherals and Access 18" Flat Panel Graphics Monitor 899.99 Peripherals and Access Model NM500X High Yield Toner Cartridge 192.99 Peripherals and Access SIMM- 16MB PCMCIAII card 149.99 Photo Mini DV Camcorder with 3.5" Swivel LCD 1099.99 Photo 5MP Telephoto Digital Camera 899.99 Software/Other Unix/Windows 1-user pack 199.99 Software/Other Laptop carrying case 55.99 Software/Other DVD-R Discs, 4.7GB, Pack of 5 49.99 Software/Other O/S Documentation Set - English 44.99 Software/Other O/S Documentation Set - German 44.99 Software/Other O/S Documentation Set - French 44.99 Software/Other O/S Documentation Set - Spanish 44.99 Software/Other O/S Documentation Set - Italian 44.99 Software/Other O/S Documentation Set - Kanji 44.99 19 rows selected.
The main query retrieves the Category, Product, and List Price details for each product that is greater than the average list price of all products within its category. This wouldn't otherwise be possible without the correlated join of the subqueries product category with the main queries product category, referenced by the main queries table alias.
Notice as well that the order by exists on the outer query. If it were placed in the subquery, it wouldn't work. The displayed results are what we want to order, not the subquery results.
| Team Fly | |