O'Reilly logo

Discovering SQL: A Hands-On Guide for Beginners by Alex Kriegel

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

CORRELATED QUERY

Normally, a subquery has no awareness of the outer query of which it is a part. It executes as if it were a stand-alone SELECT statement and could not care less what and how it was invoked. A correlated subquery changes this. In the case of a “standard” subquery, the statement is evaluated once, and the results are passed on to serve as a filter for the outer query. In correlated mode, the outer query must be invoked multiple times, once for each record returned by the inner query.

A correlated subquery (one in which the WHERE condition depends on values obtained from the rows of the containing query) executes once for each row. A noncorrelated subquery (one in which the WHERE condition is independent of the containing query) executes once at the beginning. The SQL engine makes this distinction automatically.

The classic use of the correlated subquery is in the WHERE clause. Let's consider the query we created earlier:

SELECT bk_title, bk_publisher FROM books 
    WHERE EXISTS(SELECT * FROM location 
        WHERE location.loc_shelf = 5 and location.fk_bk_loc = books.bk_id ) 
bk_title                                              bk_publisher
---------------------------------------------------------------------------------
SQL Bible                                             Wiley
Wiley Pathways: Introduction to Database Management   Wiley
Microsoft SQL Server 2000 Weekend Crash Course        Wiley
SQL Functions: Programmers Reference                  Wiley

(4 row(s) affected)

By using the EXISTS operator, we now do not have to specify BK_ID in the WHERE clause of the outer SELECT statement. ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required