MySQL: Inner join vs Where [duplicate]

This question already has answers here: Explicit vs implicit SQL joins 开发者_JS百科 (12 answers) Closed 9 years ago.

Is there a difference in performance (in mysql) between

Select * from Table1 T1 
Inner Join Table2 T2 On T1.ID = T2.ID

And

Select * from Table1 T1, Table2 T2 
Where T1.ID = T2.ID

?

---

As pulled from the accepted answer in question 44917:

Performance wise, they are exactly the same (at least in SQL Server) but be aware that they are deprecating the implicit outer join syntax.

In MySql the results are the same.

I would personally stick with joining tables explicitly... that is the "socialy acceptable" way of doing it.

---

They are the same. This can be seen by running the EXPLAIN command:

mysql> explain Select * from Table1 T1 
    -> Inner Join Table2 T2 On T1.ID = T2.ID;
+----+-------------+-------+-------+---------------+---------+---------+------+------+---------------------------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                                       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+---------------------------------------------+
|  1 | SIMPLE      | T1    | index | PRIMARY       | PRIMARY | 4       | NULL |    4 | Using index                                 |
|  1 | SIMPLE      | T2    | index | PRIMARY       | PRIMARY | 4       | NULL |    4 | Using where; Using index; Using join buffer |
+----+-------------+-------+-------+---------------+---------+---------+------+------+---------------------------------------------+
2 rows in set (0.00 sec)

mysql> explain Select * from Table1 T1, Table2 T2 
    -> Where T1.ID = T2.ID;
+----+-------------+-------+-------+---------------+---------+---------+------+------+---------------------------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                                       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+---------------------------------------------+
|  1 | SIMPLE      | T1    | index | PRIMARY       | PRIMARY | 4       | NULL |    4 | Using index                                 |
|  1 | SIMPLE      | T2    | index | PRIMARY       | PRIMARY | 4       | NULL |    4 | Using where; Using index; Using join buffer |
+----+-------------+-------+-------+---------------+---------+---------+------+------+---------------------------------------------+
2 rows in set (0.00 sec)

---

Well one late answer from me, As I am analyzing performance of a older application which uses comma based join instead of INNER JOIN clause.

So here are two tables which have a join (both have records more than 1 lac). When executing query which has a comma based join, it takes a lot longer than the INNER JOIN case.

When I analyzed the explain statement, I found that the query having comma join was using the join buffer. However the query having INNER JOIN clause had 'using Where'.

Also these queries are significantly different, as shown in rows column in explain query. These are my queries and their respective explain results.

explain select count(*) FROM mbst a , his_moneypv2 b 
        WHERE b.yymm IN ('200802','200811','201001','201002','201003') 
        AND a.tel3 != '' 
        AND a.mb_no = b.mb_no 
        AND b.true_grade_class IN (3,6)
        OR b.grade_class IN (4,7);

+----+-------------+-------+-------------+----------------------------------------------------------------+--------------------------------------+---------+------+--------+---------------------------------------------------------------------+
| id | select_type | table | type        | possible_keys                                                  | key                                  | key_len | ref  | rows   | Extra                                                               |
+----+-------------+-------+-------------+----------------------------------------------------------------+--------------------------------------+---------+------+--------+---------------------------------------------------------------------+
|  1 | SIMPLE      | b     | index_merge | PRIMARY,mb_no,yymm,yymm_2,idx_true_grade_class,idx_grade_class | idx_true_grade_class,idx_grade_class | 5,9     | NULL |  16924 | Using sort_union(idx_true_grade_class,idx_grade_class); Using where |
|  1 | SIMPLE      | a     | ALL         | PRIMARY                                                        | NULL                                 | NULL    | NULL | 134472 | Using where; Using join buffer                                      |
+----+-------------+-------+-------------+----------------------------------------------------------------+--------------------------------------+---------+------+--------+---------------------------------------------------------------------+

v/s

explain select count(*) FROM mbst a inner join his_moneypv2 b 
        on a.mb_no = b.mb_no 
        WHERE b.yymm IN ('200802','200811','201001','201002','201003') 
        AND a.tel3 != '' 
        AND b.true_grade_class IN (3,6) 
        OR b.grade_class IN (4,7);

 +----+-------------+-------+-------------+----------------------------------------------------------------+--------------------------------------+---------+--------------------+-------+---------------------------------------------------------------------+
 | id | select_type | table | type        | possible_keys                                                  | key                                  | key_len | ref                | rows  | Extra                                                               |
 +----+-------------+-------+-------------+----------------------------------------------------------------+--------------------------------------+---------+--------------------+-------+---------------------------------------------------------------------+
 |  1 | SIMPLE      | b     | index_merge | PRIMARY,mb_no,yymm,yymm_2,idx_true_grade_class,idx_grade_class | idx_true_grade_class,idx_grade_class | 5,9     | NULL               | 16924 | Using sort_union(idx_true_grade_class,idx_grade_class); Using where |
 |  1 | SIMPLE      | a     | eq_ref      | PRIMARY                                                        | PRIMARY                              | 62      | shaklee_my.b.mb_no |     1 | Using where                                                         |
 +----+-------------+-------+-------------+----------------------------------------------------------------+--------------------------------------+---------+--------------------+------

---

Actually they are virtually the same, The JOIN / ON is newer ANSI syntac, the WHERE is older ANSI syntax. Both are recognized by query engines

---

The comma in a FROM clause is a CROSS JOIN. We can imagine that SQL server has a select query execution procedure which somehow should look like that: 1. iterate through every table 2. find rows that meet join predicate and put it into result table. 3. from the result table, get only those rows that meets the where condition.

If it really looks like that, then using a CROSS JOIN on a table that has a few thousands rows could allocate a lot of memory, when every row is combined with each other before the where condition is examined. Your SQL server could be quite busy then.

---

I would think so because the first example explicitly tells mysql which columns to join and how to join them where the second one mysql has to try and figure out where you want to join.

---

the second query is just another notation for an inner join, so if there is a difference in porformance it's only because one query can be parsed faster than the other one - and that difference, if it exists, will be so tiny that you won't notice it.

for more information you could try to take a look at this question (and use the search on SO next time before asking a question that already is answered)

---

The first query is easier to understand for MySQL so it is likely that the execution plan will be better and that the query will run faster.

The second query without the where clause, is a cross join. If MySQL is able to understand the where clause good enough, it will do its best to avoid cross joining all the rows, but nothing guarantee that.

In a case as simple as yours, the performance will be strictly identical.

Performance wise, the first query will always be better or identical to the second one. And from my point of view it is also a lot easier to understand when rereading.