WHERE子句和ON子句中的条件

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.

现在我必须找到那些在MySql中进行“硬”测试的学生（学生ID，姓名和testid）。

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)

我可以知道原因吗？（假设有数百万学生和百万种类型的考试）

3 个解决方案

#1

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.

两者都相同但性能不同。如果您想选择具有更好性能的查询，我建议您使用EXPLAIN PLAN进行查询。

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.

此外，您可以使用INDEXES改进您在WHERE子句中最常使用的列的查询，您也可以查看关系代数。

#2

Those queries are exactly the same and should have exactly the same query plan. It is unlikely that there will be any measurable difference in execution time.

这些查询完全相同，并且应该具有完全相同的查询计划。执行时间不太可能存在任何可测量的差异。

There will however be a difference if you change JOIN to LEFT JOIN. In that case you need to use the first version. The second version will not give the same results.

但是，如果将JOIN更改为LEFT JOIN，则会有所不同。在这种情况下，您需要使用第一个版本。第二个版本不会给出相同的结果。

#3

Performance-wise these two are about the same. You can verify that by using the EXPLAIN command.

在性能方面，这两者大致相同。您可以使用EXPLAIN命令验证这一点。

So the question becomes one of clarity of expression of your data model. It may seem like angels dancing on the head of a pin. But, if you're pulling all the test results with your JOIN commmand, and then asking for a subset, go with WHERE type='hard'.

因此，问题就变成了数据模型表达的清晰度。看起来像天使在针头上跳舞。但是，如果您使用JOIN命令提取所有测试结果，然后询问子集，请使用WHERE type ='hard'。

But if, conceptually, you're pulling only the hard ones, then go with the ON formulation.

但是，从概念上讲，如果你只拉动那些硬的那些，那么就选择ON公式。

Notice that these two will work differently if you use LEFT JOIN. You'll get a row for each student that hasn't ever taken a hard test, as well as a row for each hard test each student has taken if you use the ON formulation with LEFT JOIN.

请注意，如果使用LEFT JOIN，这两个将以不同的方式工作。对于每个没有参加过艰苦测试的学生，你会得到一排，如果你使用LEFT JOIN的ON公式，每个学生的每次硬测试都会排成一排。

#1