tags:

views:

4652

answers:

9

Is there any efficiency difference in an explicit vs implicit inner join? For example:

select * from
table a inner join table b
on a.id = b.id;

vs.

select a.*, b.*
from table a, table b
where a.id = b.id;
+6  A: 

Performance wise, they are exactly the same (at least in SQL Server) but be aware that they are deprecating the implicit outer join syntax.

lomaxx
implicit join syntax is supported by SQL Server 2005 out of the box, but yes, it's a bad idea.
Cade Roux
Although I prefer the explicit syntax, can you explain how can they be deprecating implicit joins? The idea that it could be deprecated seems odd and the suggestion that they aren't supported by SQL 2K5 is not corrrect.
BlackWasp
Can you provide supporting documentation? This sounds wrong on multiple levels.
Chris Lively
How do you deprecate the SQL standard?
David Crawshaw
They are deprecating *outer* joins with the old syntax, not inner joins. The reason is that they are problematic to get correct, and are in some cases possible to satisfy with different execution plans that produce different results.
Lasse V. Karlsen
@david Crenshaw, the implicit join is no longer in the standard and hasn't been for 18 years.
HLGEM
In standard SQL, an implicit join isn't an outer join. It's either an inner join or a cross join.
Marcus Adams
+9  A: 

Personally I prefer the join syntax as its makes it clearer that the tables are joined and how they are joined. Try compare larger SQL queries where you selecting from 8 different tables and you have lots of filtering in the where. By using join syntax you separate out the parts where the tables are joined, to the part where you are filtering the rows.

grom
A: 

@lomaxx, just for clarity's sake, could you specify which syntax of the 2 in the question is deprecated?

(thanks, you're right it isn't overly clear. It's now been updated)

J Wynia
+2  A: 

Performance wise, they are exactly the same (at least in SQL Server) but be aware that they are deprecating this join syntax and it's not supported by sql server2005 out of the box.

I think you are thinking of the deprecated *= and =* operators vs. "outer join".

I have just now tested the two formats given, and they work properly on a SQL Server 2008 database. In my case they yielded identical execution plans, but I couldn't confidently say that this would always be true.

Joshdan
+1  A: 

On some databases (notably Oracle) the order of the joins can make a huge difference to query performance (if there are more than two tables). On one application, we had literally two orders of magnitude difference in some cases. Using the inner join syntax gives you control over this - if you use the right hints syntax.

You didn't specify which database you're using, but probability suggests SQL Server or MySQL where there it makes no real difference.

Leigh Caldwell
Leigh, you can use the hints in implicit joins too.
SquareCog
+3  A: 

@lomaxx: Just to clarify, I'm pretty certain that both above syntax are supported by SQL Serv 2005. The syntax below is NOT supported however

select a., b.
from table a, table b
where a.id *= b.id;

Specifically, the outer join (*=) is not supported.

deadbug
Frankly I wouldn't use it even in SQL Server 2000, the *= syntax often gives wrong answers. Sometimes it interprets these as cross joins.
HLGEM
+2  A: 

The first answer you gave uses what is known as ANSI join syntax, the other is valid and will work in any relational database.

I agree with grom that you should use ANSI join syntax. As they said, the main reason is for clarity. Rather than having a where clause with lots of predicates, some of which join tables and others restricting the rows returned with the ANSI join syntax you are making it blindingly clear which conditions are being used to join your tables and which are being used to restrict the results.

andy47
A: 

As Leigh Caldwell has stated, the query optimizer can produce different query plans based on what functionally looks like the same SQL statement. For further reading on this, have a look at the following two blog postings:-

One posting from the Oracle Optimizer Team

Another posting from the "Structured Data" blog

I hope you find this interesting.

Mike McAllister
Mike, the difference they are talking about is that you need to be sure that if you specify an explicit join, you specify the *join condition* to join on, not the filter. You will note that for semantically correct queries, the exec plan is the same.
SquareCog
+1  A: 

The second syntax has the unwanted possibility of a cross join: you can add tables to the FROM part without corresponding WHERE clause. This is considered harmful.

edosoft