CODEWITHSUNDEEP
sql
A.Kumar
A.Kumar

Reputation: 53

Performance comparison between 2 queries

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?

Upvotes: 0

Views: 46

Answers (1)

HLGEM
HLGEM

Reputation: 96600

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.

Upvotes: 2

Related Questions