Database Reference
In-Depth Information
information as the intermediate result. Based on the output of the subquery, the main query will
return the information about the classes.
Notice that you must enclose the subquery within parentheses. he
=
operator in the WHERE
clause is used since only a single value is returned by the subquery. Sometimes, a subquery returns
multiple values. In such a case, the IN operator should be used in the search condition. he next
example in Figure 6.33 shows that two diferent student ids match the last name Cox returned by
the subquery. herefore, the IN operator is used in the WHERE clause.
To select data from more than two tables, more than two subqueries can be used to accomplish
the task. You can use an AND operator to relate two subqueries, or nest a subquery in another
subquery. To illustrate the use of a nested query, let us consider the scenario where we need to select
students who got an A grade in the Database class. his task can be accomplished by a nested query
that uses its innermost subquery to return the course id that matches the course name Database.
he irst intermediate subquery returns the classes that teach Database. he next intermediate
subquery returns a list of students who got an A grade from the classes. he outermost query will
return the information about those selected students. he query and its result are listed in Figure
6.34. Note that in a nested query, the innermost subquery is executed irst and the outermost query
is executed last.
he logical operator EXISTS can be used in the WHERE clause to verify if there is any output
returned by a subquery. EXISTS will return a True or False value. If the output of the subquery
contains one or more values, the search condition in the WHERE clause becomes true; otherwise,
the search condition is false. he logical operator NOT EXISTS is just the opposite of EXISTS.
In Figure 6.35, if Garza is an advisor, the query will return the information about his student(s).
You need to pay attention to several things in the above query. First, the alias S stands for the
table STUDENT and F stands for FACULTY. he aliases are used to distinguish between the two
FacultyIDs in the subquery's WHERE clause. he subquery will check if any faculty id from the
STUDENT table in the main query matches the id of the faculty who has the last name Garza in
the FACULTY table. his leads to the second thing that you need to pay attention to. When using
EXISTS or NOT EXISTS with a subquery, you should link the table in the subquery and the table
in the main query with a matching condition in the WHERE clause, for example, the condition
S.FacultyID
=
F.FacultyID in Figure 6.35.
Figure 6.33
Use subquery that returns multiple values.
