What is more efficient in terms of inner join and where clause in sql

Question

Possible Duplicate:
Inner join vs Where

Hello in many interviews they ask which is more efficient , inner join or where clause? How to justify an tell an answer for such a scenario?

http://stackoverflow.com/questions/121631/inner-join-vs-where is a good discussion of this. — dash, Dec 13 '11 at 12:13
IMO depends on the database versions; which can result in different execution plans. To answer the question directly I would simply say depending on database version, you'd have to run some Queries see some execution plans and find out. inner joins are the new ansi standard however using non-ansi joins was the standard for many years; thus your likely to encounter both in a long established environment; both typically work and work well because engines compile them to the same execution plans. But to be certain you'd have to check both statements plans. — xQbert, Dec 13 '11 at 12:20
Also, the older `,` notation is a pain in the arse to understand and debug. It's not a consensus, but the great majority agree that using `,` notation is bad practice. — MatBailie, Dec 13 '11 at 12:27

score 2 · Answer 1 · answered Dec 13 '11 at 12:33

2

Both are same as per execution Plan, but can increase readability by using join. Best practice is to use join

answered Dec 13 '11 at 12:33

Nighil

is there a way to prove both are the same? any logical explanation? – Evlikosh Dawark Dec 13 '11 at 12:36
I try to answer :) Both queries must return the same result set, so in the ideal conditions the query optimizer must produce the same execution plan. Sometimes (very complex query, not updated statistics and so on) the query optimizer can fail and produce not optimal execution plan in one of the two queries (or in both of them) – ceth Dec 13 '11 at 13:23
But I don't know any sources which recommend to use 'join' ower 'where' – ceth Dec 13 '11 at 13:26

score 1 · Accepted Answer · answered Dec 13 '11 at 12:13

1

It idepends.... You must write queries and compare the execution plans to get the answer.

answered Dec 13 '11 at 12:13

ceth

I think the OP means `SELECT * FROM a, b WHERE a.id = b.id` compared to using JOIN syntax. – MatBailie Dec 13 '11 at 12:25

2 Answers2