What is Internal process of below mentioned queries?

Question

Select * from table t1 inner join table t2 on t1.id=t2.id

Select * from table t1,table t2 where t1.id=t2.id

As per performance which query is optimistic query?

score 2 · Answer 1 · answered Nov 21 '19 at 00:11

First query is using standar ANSI join and second query is using old style explicit join.

Standard ANSI joins are recomended to use as it is more neat and clean code.

There is no difference between two query.

Both will produce same output and there will be no difference in terms of performance.

Cheers!!

score 2 · Accepted Answer · answered Nov 21 '19 at 00:12

create table t1 (id int primary key);
create table t2 (id int primary key);

explain select * from t1 inner join t2 on t1.id = t2.id;

id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                               | rows | filtered | Extra      
-: | :---------- | :---- | :--------- | :----- | :------------ | :------ | :------ | :-------------------------------- | ---: | -------: | :----------
 1 | SIMPLE      | t1    | null       | index  | PRIMARY       | PRIMARY | 4       | null                              |    1 |   100.00 | Using index
 1 | SIMPLE      | t2    | null       | eq_ref | PRIMARY       | PRIMARY | 4       | fiddle_NTBFHZQPZPTPOTLPGUEV.t1.id |    1 |   100.00 | Using index

explain select * from t1, t2 where t1.id = t2.id;

id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                               | rows | filtered | Extra      
-: | :---------- | :---- | :--------- | :----- | :------------ | :------ | :------ | :-------------------------------- | ---: | -------: | :----------
 1 | SIMPLE      | t1    | null       | index  | PRIMARY       | PRIMARY | 4       | null                              |    1 |   100.00 | Using index
 1 | SIMPLE      | t2    | null       | eq_ref | PRIMARY       | PRIMARY | 4       | fiddle_NTBFHZQPZPTPOTLPGUEV.t1.id |    1 |   100.00 | Using index

db<>fiddle here

Both queries are functionnaly identical (id they produce the same result), and the optimizer produces the same explain plan for both of them (at least in this simple cas, and, probably also in more complicated queries).

However, most SQL experts will tell you that the implicit join syntax (using a comma in the from clause) is more complicated to follow, and that, since 1992, the ANSI SQL standard recommends using explicit joins (with the join ... on ... syntax). I would strongly suggest to follow that advice.

What is Internal process of below mentioned queries?

2 Answers2