Learning SQL on SQL Server 2005

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.

Noncorrelated Subqueries

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