Chapter 10. Correlated Subqueries
A correlated subquery is an inner subquery whose information is referenced by the main, outer query such that the inner query may be thought of as being executed repeatedly. In this chapter, we discuss correlated subqueries
in detail. We discuss existence queries (
EXISTS) and correlation as well as
NOT EXISTS. We also take a look at SQL’s universal and existential qualifiers. Before discussing correlated subqueries in detail however, let’s make sure that you understand what constitutes a noncorrelated subquery.
A noncorrelated subquery is a subquery that is independent of the outer query. In other words, the subquery could be executed on its own. The following is an example of a query that is not correlated:
SELECT s.sname FROM Student s WHERE s.stno IN (SELECT gr.student_number FROM Grade_report gr WHERE gr.grade = 'A')
The first part of the preceding query (the first three lines) is the main, outer query, and the second part (the part in parentheses) is the subquery (also referred to as an inner, nested, or embedded query). To demonstrate that this subquery is an independent entity, you could run it by itself:
SELECT gr.student_number FROM Grade_report gr WHERE gr.grade = 'A'
which would produce the following output (17 rows):
student_number -------------- 2 3 8 8 10 14 20 129 142 129 34 49 123 125 126 127 142 (17 row(s) affected)
The preceding subquery is thought of as being evaluated first, creating the set of student numbers ...