In job interview for a database / sql programmer a question is sometime asked what is a correlated Sub query? A sub query containing an outer reference is called correlated sub query, because its results are correlated with each individual row of the main query. For the same reason, an outer reference is sometimes called a correlated reference. [1]

This query finds the names of all authors who earn 100 percent of the shared royalty (royaltyper) on a book. [2]

USE pubs
SELECT au_lname, au_fname
FROM authors
WHERE 100 IN
(SELECT royaltyper
FROM titleauthor
WHERE titleauthor.au_ID = authors.au_id)

Here is the result set:

au_lname au_fname
—————————————- ——————–
White Johnson
Green Marjorie
Carson Cheryl
Straight Dean
Locksley Charlene
Blotchet-Halls Reginald
del Castillo Innes
Panteley Sylvia
Ringer Albert

(9 row(s) affected)

Further Recommended post