CODEWITHSUNDEEP
sql-server
user1050619
user1050619

Reputation: 20856

Inner join vs select statements on multiple tables

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

Upvotes: 1

Views: 3103

Answers (4)

Joel Coehoorn
Joel Coehoorn

Reputation: 415600

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.

Upvotes: 2

Mike Henderson
Mike Henderson

Reputation: 1317

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.

Upvotes: 0

bpanulla
bpanulla

Reputation: 2998

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.

Upvotes: 1

ytoledano
ytoledano

Reputation: 3133

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.

Upvotes: 1

Related Questions