MySQL: Inner join vs Where

Question

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

?

dup of http://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins — Patrick, Mar 11 '11 at 14:13

score 15 · Accepted Answer · answered Mar 11 '11 at 14:16

15

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.

answered Mar 11 '11 at 14:16

Patrick

7,512
7
39
50

I found the same answer for Oracle, and the are some agree that there is not difference for execution. For Oracle there is a way to inspect the "plan" for the query, is there something similar in MySQL? – Victor Mar 11 '11 at 14:19
it depends on your software. What are you using for MySql... Toad for MySQL has an "Explain Plan" tab for any query you create, but its probably not exactly what your looking for. It uses the "EXPLAIN" command That Ewan mentions in his answer. – Patrick Mar 11 '11 at 14:24
I agree that the explicit JOIN is better. It is in fact much safer, and less prone to becoming a CROSS JOIN if you forget some part of the WHERE clause. – Daniel Williams Mar 11 '11 at 15:30
I believe that using JOIN is much more easy to maintain the code. It's more readable. I only would use CROSS JOIN for a performance enhancement. – Victor Mar 11 '11 at 15:39
"Socialy acceptable" gets to the heart of it. – ESP32 Jul 06 '17 at 11:03

score 15 · Answer 2 · answered Mar 11 '11 at 14:20

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)

score 2 · Answer 3 · edited Jul 17 '14 at 17:12

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                                                         |
 +----+-------------+-------+-------------+----------------------------------------------------------------+--------------------------------------+---------+--------------------+------

After more analyze, I found that two constraint are responsible for this huge performance difference between both queries. First join condition must be first in where clause before any other filter condition. Second using bracket to group statement which have or — kuldeep.kamboj, May 04 '12 at 07:46
Your queries do not return the same rows. Cross join rows with `b.grade_class IN (4,7)` are always in the output from the first `from`, but when `a.mb_no = b.mb_no` they are not in the output from the second `from`. So comparing the queries tells you nothing about the question. — philipxy, Dec 21 '17 at 06:07

score 1 · Answer 4 · answered Mar 11 '11 at 14:17

1

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

answered Mar 11 '11 at 14:17

DRapp

47,638
12
72
142

score 0 · Answer 5 · answered Mar 11 '11 at 14:13

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 don't think it works like that. If so, what about if the where condition filters the first table to only one record, then it will only combine one record with the second table, making it really fast? I believe SQL handles data in other ways (which I can only guess). — dxvargas, Feb 15 '15 at 09:51

score 0 · Answer 6 · answered Mar 11 '11 at 14:14

0

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.

answered Mar 11 '11 at 14:14

Alistair Laing

983
1
7
18

score 0 · Answer 7 · edited May 23 '17 at 12:23

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)

score 0 · Answer 8 · answered Mar 11 '11 at 14:17

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.

MySQL: Inner join vs Where

8 Answers8

Linked

Related