Performance comparison between 2 queries

Question

I have 2 tables A,B where ID field is the common key. If i use any of the below mentioned query the result will be same.

select * from A,B where A.ID=B.ID and A.Id = 10;
select * from A inner join B on A.ID=B.ID where A.Id = 10;

Can any one please let me know which query will be better in terms of performance and why?

I would venture to say to look at your explanation plan, however the queries are absolutely identical, and differ just in syntax. Most will tell you to use explicit join syntax. What DBMS are you using? — Jeremy, Jan 31 '17 at 14:34
Normally there should no be difference in performance. I prefer the inner join syntax, because you'll get a syntax error if you forget the join-condition. In the other case you'll get a cartesian propduct if you forget the join-condition. — Natrium, Jan 31 '17 at 14:37
@Jeremy `explanation plan` <= I think you meant `execution plan`? — Igor, Jan 31 '17 at 14:38
In any database that I can think of, they would have exactly the same execution plans. You should follow a simple rule: *Never* use commas in the `FROM` clause. *Always* use proper, explicit `JOIN` syntax. — Gordon Linoff, Jan 31 '17 at 14:40
Possible duplicate of [Explicit vs implicit SQL joins](http://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins) — AndySavage, Jan 31 '17 at 14:57

score 2 · Accepted Answer · answered Jan 31 '17 at 14:38

They should perform roughly the same. The first query however is a SQL antipattern and should never be used. Implicit joins often cause problems because as they get more complicated you find that the where clause is often omitted accidentally whereas this will not pass the syntax check in most databases using the explicit join syntax. THe omission of teh where clause by accident causes a cross join and the then teh results are incorrect.

Further, you can have inconsistent results later in maintenance if you need to change one join to a left join and you use an explicit join (especially in SQL server where the implicit left join syntax is no longer allowed and was always buggy and should never be used) and end up mixing the joins (when you have multiple joins as is common for most queries).

Additionally if you actually want a cross join, someone doing maintenance later is not likely to know it and think your code was mistaken and needed an inner join. Intent is clear when you use explicit joins.

Performance comparison between 2 queries

1 Answers1