Difference B/W, WHERE and ON in SQL join Operation

Question

Here is my test code for explaination

create table Test3(id integer, name varchar(100));
insert into Test3(id, name) values(1, "Vijay");
insert into Test3(id, name) values(2, "Sandy");
insert into Test3(id, name) values(3, "Rohit");
create table Test4(id integer, surname varchar(100));
insert into Test4(id, surname) values(1, "karma");
insert into Test4(id, surname) values(2, "sharma");

Here is my Queries:

select Test3.id , Test3.name , Test4.surname from Test3,Test4 where Test3.id = Test4.id;
select Test3.id , Test3.name , Test4.surname from Test3 join Test4 on Test3.id = Test4.id;

Both the above query statements resulted same as below:

1 Vijay karma

2 Sandy sharma

What is the difference in both queries internally and which one on more efficient?

Answer 1

The two queries are functionaly equivalent.

One uses old-school, implicit joins (with a comma in the from clause), the other uses explicit joins (with the on keyword).

While both queries are guaranteed to generate the same results (and would likely have the same performance), I would strongly recommend to use the explicit join syntax. Implicit joins have fallen out of favor decades ago (that was the ANSI 92 days) for many reasons, notably because they are harder to follow (the logic is spread between the from clause and the where clause), and cannot be easily converted to a left join.