©Silberschatz, Korth and Sudarshan1.51Database System Concepts - 6 th Edition Optimizing Nested Subqueries (Cont.) Correlated evaluation may be quite inefficient since a large number of calls may be made to the nested query there may be unnecessary random I/O as a result SQL optimizers attempt to transform nested subqueries to joins where possible, enabling use of efficient join techniques E.g.: earlier nested query can be rewritten as select name from instructor, teaches where instructor.ID = teaches.ID and teaches.year = 2007 Note: the two queries generate different numbers of duplicates (why )  teaches can have duplicate IDs  Can be modified to handle duplicates correctly as we will see In general, it is not possible/straightforward to move the entire nested subquery from clause into the outer level query from clause A temporary relation is created instead, and used in body of outer level query
{}