Inner join vs select statements on multiple tables

Question

THe below 2 queries performs the same operation, but wondering which would be the fastest and most preferable?

NUM is the primary key on table1 & table2...

select * 
from table1 tb1,
table2 tb2
where tb1.num = tb2.num


select * 
from table1 tb1
inner join
table2 tb2
on tb1.num = tb2.num

possible duplicate of [SQL left join vs multiple tables on FROM line?](http://stackoverflow.com/questions/894490/sql-left-join-vs-multiple-tables-on-from-line) — Ciro Santilli OurBigBook.com, Dec 04 '14 at 13:58

score 2 · Answer 1 · answered Jul 16 '13 at 16:45

They are the same query. The first is an older alternate syntax, but they both mean do an inner join.

You should avoid using the older syntax. It's not just readability, but as you build more complex queries, there are things that you simply can't do with the old syntax. Additionally, the old syntax is going through a slow process of being phased out, with the equivalent outer join syntax marked as deprecated in most products, and iirc dropped already in at least one.

score 1 · Answer 2 · answered Jul 16 '13 at 16:24

1

The 2 SQL statements are equivalent. You can look at the execution plan to confirm. As a rule, given 2 SQL statements which affect/return the same rows in the same way, the server is free to execute them the same way.

answered Jul 16 '13 at 16:24

ytoledano

3,003
2
24
39

1

The INNER JOIN syntax is preferable for readability reasons rather than performance. – Matt Allwood Jul 16 '13 at 16:30

score 1 · Answer 3 · answered Jul 16 '13 at 16:27

They're equivalent queries - both are inner joins, but the first uses an older, implicit join syntax. Your database should execute them in exactly the same way.

If you're unsure, you could always use the SQL Management Studio to view and compare the execution plans of both queries.

score 0 · Answer 4 · answered Jul 16 '13 at 16:36

The first example is what I have seen referred to as an Oracle Join. As mentioned already there appears to be little performance difference. I prefer the second example from a readability standpoint because it separates join conditions from filter conditions.

Inner join vs select statements on multiple tables

4 Answers4