Siraj Siraj - 17 days ago 5
MySQL Question

convert query into nested query

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?

SELECT student.studentname,
schedule.subcode,
AVG(attendance.ispresent)*100 AS Attendance_Status
FROM student
JOIN attendance
ON student.usn = attendance.usn
JOIN schedule
ON schedule.sched_id = attendance.sched_id
WHERE student.usn="4jc14is008"
GROUP BY schedule.subcode
ORDER BY schedule.subcode;

Answer

This will be evaluated as an NLJ ("Nested Loop Join").

  1. Look in student, filtering on usn = '...'.
  2. For each such row (probably one in this case), do NLJ into attendance to locate any and all rows that satisfy the ON given. This leads to a longer (or shorter) list of rows.
  3. For each of the rows in #2, do NLJ into schedule based on ON. Now there is a set of rows...
  4. Do the GROUP BY and ORDER BY (these can be done simultaneously since they are identical). This will deliver no more rows than what #3 gave. (GROUP BY can never increase the number of rows.)

Do not rewire the query into WHERE x IN ( SELECT ... ), that usually make it run slower.