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:
--从表中得到等级为A的学生的名字
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_numberFROM Grade_report grWHERE gr.grade = 'A'两个表是相互独立的两部分 能够独立的进行运行2 Correlated Subqueriesa 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 beingexecuted repeatedly.The correlated subquery cannot stand alone, as it depends on the outer query;therefore, completing the subquery prior to execution of the outer query is notan option. The efficiency of the correlated subquery varies; it may be worthwhileto test the efficiency of correlated subqueries versus joins or sets.The correlated subquery acts like a nested DO loop in a programming language,where the first row from the Student table is SELECTed and tested against allthe rows in the Grade_report table, and then the second Student row is SELECTedand tested against all rows in the Grade_report table. The following is the DO loopin pseudocode:LOOP1: For each row in Student s DOLOOP2: For each row in Grade_report gr DOIF (gr.student_number = s.stno) THENIF (gr.grade = 'B') THEN TRUEEND LOOP2;IF TRUE, THEN Student row is SELECTedEND LOOP1for example--一个Correlated Subqueries例子SELECT s.snameFROM Student sWHERE s.stno IN(SELECT gr.student_numberFROM Grade_report gr, SectionWHERE Section.section_id = gr.section_idAND Section.course_num LIKE 'COSC%'AND gr.student_number = s.stnoAND gr.grade = 'B')3