10.1. 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 who have As. Then, the subquery's result set is used to determine which rows (tuples) in the main query will be SELECTed. So, the full query results in the following output (14 rows):

sname
--------------------
Lineas
Mary
Brenda
Richard
Lujack
Donald
Lynette
Susan
Holly
Sadie
Jessica
Steve
Cedric
Jerry
 
(14 row(s) affected)