-1

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?

GMB
  • 216,147
  • 25
  • 84
  • 135
vijay karma
  • 59
  • 1
  • 4
  • Compare the execution plans. I'd be surprised if there are any differences. – jarlh Feb 17 '20 at 13:51
  • 1
    This is the opposite @GordonLinoff – Gosfly Feb 17 '20 at 13:56
  • (Clearly,) This is a faq. Before considering posting please read the manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask] & the voting arrow mouseover texts. – philipxy Apr 16 '20 at 21:47
  • Does this answer your question? [Explicit vs implicit SQL joins](https://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins) – philipxy Apr 16 '20 at 21:53

2 Answers2

4

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.

GMB
  • 216,147
  • 25
  • 84
  • 135
  • 2
    Just wanted to emphasize there's no reason nowadays to use old-style joins. Modern join syntax is much clearer, easier to read and to debug, and has been available since SQL-92 (28 years ago). – The Impaler Feb 17 '20 at 13:55
0

The performance will be equivalent in both the cases.

Select from multiple tables without join is nothing but a cross join. When you put a where clause to it, the query performs inner join.

SUMIT PATRO
  • 166
  • 5