Is there any difference in the following statements and if so, which is preferred performance wise? Can this even be answered without knowing the row counts of the tables before and after the where clause and possible index usage?
SELECT * FROM a JOIN b ON a.id = b.id AND a.col2 BETWEEN 1 AND 5; SELECT * FROM a JOIN b ON a.id = b.id WHERE a.col2 BETWEEN 1 AND 5;
My reason for asking is because if for instance the tables are massive but the where clause filters 99.9999% of the records out, I don't want to waste time joining massive tables if after the join 99.9999% of the records are not used.
Take the same situation, which statement (if any difference) requires less IOPS. (index present on b.id and one on a.col2)