Geek asks and answers

Conditions in the WHERE clause and ON clause

Suppose 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 need to find these students (student ID, name and testid) who accepted the "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'

Can I find out the reason? (Suppose millions of students and millions of test types)

+5
source share
3 answers

Both are the same, but with different performance. I recommend that you use EXPLAIN PLANfor queries if you want to select a query with better performance.

See Overview of Query Execution Plan .

INDEXES , WHERE, .

+6

. , - .

, JOIN LEFT JOIN. . .

0

. , EXPLAIN.

, . , . , JOIN, , WHERE type='hard'.

, , , ON.

Please note that these two will work differently if you use LEFT JOIN. You will receive a line for each student who has never passed the hard test, as well as a line for each hard test that each student has done if you use the wording ONwith LEFT JOIN.

0
source

More articles:


All Articles