What are the benefits of explicitly joining Transitive Closure in SQL?

Question

When I join three or more tables together with a common column, I would write my query as follows:

SELECT * FROM a, b, c WHERE a.id = b.id AND b.id = c.id 

a colleague recently asked me why I didn’t deal with explicit Transitive Closure joins in my queries like this:

 SELECT * FROM a, b, c WHERE a.id = b.id AND b.id = c.id AND c.id = a.id 

Are there really any benefits to this? Can the optimizer do this for himself?

edit: I know this is a wicked syntax, but this is a quick and dirty example of legal code outdated +1 @ Stu to clear it

Answer 1

You do not need to do this in modern database engines, but there was a time when these things gave the query optimizer more hints about the possible index paths and therefore faster results.

These days, all the syntax comes out anyway.

Answer 2

This is a dirty, evil obsolete syntax. You write it like

 Select * -- Oh, and don't ever use *, either From A Inner Join B On A.ID = B.ID Inner Join C On B.ID = C.ID 

Answer 3

No, this syntax stems from the days before joining the language. Not sure about the problems associated with it, but there are certain language constructs that are more supported for join tables.

Answer 4

If you look at it from a mathematical point of view, your examples should give the same results.

a = b = c

So, your first example will give the same results as the second, so there is no need to do extra work.

Answer 5

I just want to say that such an attachment is the work of the devils.
Just think about it; join and filter conditions are mixed together in the where statement.
What happens when you need to join 20 tables and filter by 15 values?

Again, only my $ .02

Answer 6

In Microsoft SQL, query plans for these two queries are identical — they run the same way.

Answer 7

This question is similar to this one here with a very detailed explanation:

SQL query from Joel Spolsky's article

The short answer is that explicitly declaring a transit property can speed up the request. This is because query optimization is not a trivial task, and some SQL servers may have problems with it.

Answer 8

This syntax has its uses, though ... there are times when you find that you need to combine two tables in more than one field