CRITICAL SKILL 2.10 Learn Subqueries: Simple and Correlated Comparison with Joins

Team Fly

Page 72

CRITICAL SKILL 2.10
Learn Subqueries: Simple and Correlated Comparison with Joins

Within SQL, functionality exists to create subqueries, which are essentially queries within queries. This power capability makes it possible to produce results based on another result or set of results. Let's explore this concept a little further.

Simple Subquery

Without the functionality of subqueries, it would take a couple SQL queries to retrieve product information for the product with the maximum list price. The first query would have to find the value of max(prod_list_price). A subsequent query would have to use the value resolved for max(prod_list_price) to find the product details. Let's take a look at how we can resolve this with a subquery embedded in the where clause of the main query:

select prod_id, prod_name, prod_category
from   products
where  prod_list_price = (select max(prod_list_price)
                          from   products);

The subquery is enclosed in parentheses and is part of the where clause. The main query is resolved based on the results of the subquery, in this case, the maximum product list price. As you can see, the ability to have a query within a query is very powerful.

Running SQL queries with embedded subqueries can affect performance. As your experience with subqueries increases, you will find that you will need to work closely with your database administrator, more commonly referred to as a DBA, to optimize statements with subquery processing.

Ask the Expert

Q: What would happen if the subquery returned multiple values?

A: Since the subquery in the example could return only a single value, it was acceptable for it to be written with the equals (=) operand. If multiple values are expected from the subquery, the in list operand should be used.

Team Fly