Conditions in WHERE clause and in ON clause
mysql, sql
Solution
Both are same but with different performance. I recommend to you use `EXPLAIN PLAN` for queries if you want to choose query with better performance.
Have look at Understanding the Query Execution Plan.
Also you can improve your queries with `INDEXES` for columns which you are using the most frequently in `WHERE` clause, you can have look at relational algebra also.
Problem
Assume I have two tables, ``` Student Test Id Name TestId Type StudentId -- ---- ------ ---- --------- 1 Mark 774 Hard 1 2 Sam 774 Hard 2 3 John 775 Easy 3 ``` Now I have to find those students(the student id,name and testid) who have taken "hard" type of test in MySql. Which one is better(In terms of performance)?. ``` 1.Select student.id,student.name,test.testid from student join test on test.studentid=student.id and test.type='hard' 2.Select student.id,student.name,test.testid from student join test on test.studentid=student.id where test.type='hard' ``` May I know the reason?(Assume there are millions of students and million types of tests)