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 SlideShow below to see the evaluation process for a subquery statement.

  1. This is the original SQL query using a subquery statement
  2. This is the subquery statement.
  3. If you substitute the value of 1389 in the outer query, the engine is actually using this query
  4. An analogous process is the method of completing mathematical calculations by completing calculations in parentheses in parentheses first

An alternate version is available at the following link:
Subquery options.
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.