how do you convert a join query into a nested query (using "where in" condition)?
for eg how to convert this into a nested query?
AVG(attendance.ispresent)*100 AS Attendance_Status
ON student.usn = attendance.usn
ON schedule.sched_id = attendance.sched_id
GROUP BY schedule.subcode
ORDER BY schedule.subcode;
This will be evaluated as an NLJ ("Nested Loop Join").
student, filtering on
usn = '...'.
attendanceto locate any and all rows that satisfy the
ONgiven. This leads to a longer (or shorter) list of rows.
ON. Now there is a set of rows...
ORDER BY(these can be done simultaneously since they are identical). This will deliver no more rows than what #3 gave. (
GROUP BYcan never increase the number of rows.)
Do not rewire the query into
WHERE x IN ( SELECT ... ), that usually make it run slower.