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?
Advertisement
Answer
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.
Related readings: