SQL de-correlation example

https://cs.emis.de/LNI/Proceedings/Proceedings241/383.pdf

Table: students: {[id, name, major, year, . . . ]}
Table exams: {[sid, course, curriculum, date, . . . ]}

    select s.name,e.course
    from   students s,exams e 
    where  s.id=e.sid and
           e.grade=(select min(e2.grade)
                    from exams e2
                    where s.id=e2.sid)

Conceptually, for each student, exam pair (s, e) it determines, in the subquery, whether or not this particular exam e has the best grade of all exams of this particular student s

A SQL representation of this rewrite would look like this:

    select s.name,e.course
    from   students s,exams e,
           (select e2.sid as id, min(e2.grade) as best 
            from exams e2
            group by e2.sid) m
    where  s.id=e.sid and m.id=s.id and
           e.grade=m.best

Here, the evaluation of the subquery no longer depends on the values of s, and thus regular joins can be used.

afs/sql-correlated-example.md