Select Statement  «Prev  Next»
Lesson 6 Subquery statements and options
Objective Examine two options for using subquery statements.

SQL Subquery Statement Options

When you use the SQL subquery approach, you have two options
  1. You can use an IN clause, as shown in the example shown in the previous lesson, or
  2. you can use the EQUAL qualifier.

Your choice will depend on a simple question:
Question: Are you expecting your subquery statement to return only one value, or several?
Some examples will help explain. First, with subquery statements, it is important to understand that the SELECT statement you put in the parentheses is executed first, then the results are used to work with the outer SELECT statement. Let us take another look at how the subquery statement from the previous lesson works. Examine the series of images below to see the evaluation process for a subquery statement.

1) This is the original SQL query with a sub-SELECT statement
1) This is the original SQL query using a subquery statement

2) This is the sub-SELECT statement. The Publishers table is queried first to return results from this sub-SELECT statement.
2) This is the subquery statement. The Publishers table is queried first to return results from this subquery statement, which returns Pub_ID column values that have a corresponding state of "CA". In this case, the Publishers query returns only a single row, and the Pub_ID is 1389.

3) If you substitute the value of 1389 in the outer query, the engine is actually using this query
3) If you substitute the value of 1389 in the outer query, the engine is actually using this query, the engine is actually using this query.

4) An analogous process is the method of completing mathematical calucations by completing calculations in parentheses
4) An analogous process is the method of completing mathematical calculations by completing calculations in parentheses in parentheses first, working from the inside to the outside.

Subquery Options

The subselect obtains one row of the surrounding SELECT after the next as an parameter with the name 'p'. Within the subselect all columns of the row 'p' are known and may be used. Here the family name from the outside row is used in the subquery to find all persons within the family and the average weight of the family members.
Subselect queries are executed once per row of the surrounding query. Therefore they are much more costly than non-correlated subqueries. There might exist an equivalent solution using JOIN or GROUP BY which works with better performance. The query optimizer of the DBMS internally might rearrange the given subquery into one of the equivalent forms and this does not work in all cases. The distinction between correlated and non-correlated subqueries is universal. It applies also to the other subquery classes.

SELECT id, firstname, lastname, weight,
    (SELECT avg(weight)
     FROM   person sq                          
     WHERE  sq.lastname = p.lastname  
    ) family_average                  
FROM   person p                          
ORDER BY lastname, weight;

The concept of a subquery function is as follows: One select query is nested inside another query, creating a resource otherwise unavailable for searching in a single statement. The subquery allows query consolidation and the task of result set comparisons is placed on the database rather than application code. One way to use this functionality is by locating a value related by column with comparable data in two tables.

Subquery Statement

Using the standard query, you can nest the outer query known as a subquery, which is termed the inner query:
SELECT MemberId FROM MemberDetails
WHERE MemberId = (SELECT MAX(FilmId) FROM Films);

A WHERE clause is added to the outer query, and it specifies that MemberId must equal the value returned by the nested inner query, which is contained within brackets in the preceding example. It is also possible to nest a subquery inside the inner query. Consider the following example:
SELECT MemberId FROM MemberDetails
WHERE MemberId = (SELECT MAX(FilmId) FROM Films
WHERE FilmId IN (SELECT LocationId FROM Location));
In the preceding example, a subquery is added to the WHERE clause of the inner query.