| Team Fly | |
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.
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.
| Team Fly | |