CODEWITHSUNDEEP
mysqlsqljoinexplain
Jibran K
Jibran K

Reputation: 885

Filtering before Join or after Join in SQL

I have two queries to get the same data, they more or less have the same execution time.

SELECT name AS prog_name,d.chap_name,d.vid_name,d.idvideo FROM program 
     JOIN (SELECT name AS chap_name,chapter.idprogram, c.vid_name,c.idvideo FROM chapter 
     JOIN (SELECT a.name AS vid_name, a.idvideo, b.idchapter FROM video a 
     JOIN (SELECT idvideo,idchapter,x.idchaptervideo FROM chaptervideo 
     JOIN (SELECT idchaptervideo FROM prescriptionvideo  WHERE idprescription=50)x 
     ON x.idchaptervideo=chaptervideo.idchaptervideo) b 
     ON b.idvideo=a.idvideo)c 
     ON c.idchapter=chapter.idchapter) d 
     ON d.idprogram=program.idprogram

SELECT program.name AS prog_name,chapter.name as chap_name,video.name as vid_name,video.idvideo FROM prescriptionvideo
JOIN chaptervideo ON prescriptionvideo.idchaptervideo=chaptervideo.idchaptervideo
JOIN video on chaptervideo.idvideo=video.idvideo
JOIN chapter on chaptervideo.idchapter=chapter.idchapter
JOIN program on chapter.idprogram=program.idprogram
where idprescription=50

Can somebody guide me as to which one of them is better. I have used filtering before joins in the former and after joins in the latter. The MySQL explain shows more row scans in the former compared to the latter.

Upvotes: 6

Views: 5181

Answers (2)

Jibran K
Jibran K

Reputation: 885

1st one is better as it is easy to read and write and has the lower row scan value otherwise the queries have the same result set.

Upvotes: 1

Chris Gessler
Chris Gessler

Reputation: 23123

The MySQL explain shows more row scans in the former compared to the latter.

Well, there you have it! Except for one tiny detail. Putting filter conditions in the join statement can return different results than putting the same condition in the where clause. Your two SQL queries are not quite identical, even though they return the same result set.

SELECT program.name AS prog_name,chapter.name as chap_name,video.name as vid_name,video.idvideo FROM prescriptionvideo 
JOIN chaptervideo ON prescriptionvideo.idchaptervideo=chaptervideo.idchaptervideo 
JOIN video on chaptervideo.idvideo=video.idvideo 
JOIN chapter on chaptervideo.idchapter=chapter.idchapter and chapter.idprescription=50
JOIN program on chapter.idprogram=program.idprogram

Upvotes: 1

Related Questions