Nested Correlated Query
NESTED CORRELATED QUERY
Movie (Title, Year, Director, Length)
Find movies whose title appears more than once.
Title Year
‘A’ 2018
‘A’ 2012
‘B’ 2001
‘C’ 2005
‘A’ 2006
Movie
SELECT DISTINCT X.Title
FROM Movie AS X
WHERE EXISTS (SELECT *
FROM Movie AS Y
WHERE X.Year <> Y.Year
AND X.Title = Y.Title);
Outer Loop: X = (‘A’, 2018) – Inner Loop: Y = (‘A’, 2018): WHERE: FALSE
Outer Loop: X = (‘A’, 2018) – Inner Loop: Y = (‘A’, 2012): WHERE: TRUE ‘A’
Outer Loop: X = (‘A’, 2018) – Inner Loop: Y = (‘B’, 2001): WHERE: FALSE
…
Outer Loop: X = (‘A’, 2018) – Inner Loop: Y = (‘A’, 2006): WHERE: TRUE ‘A’