Implicit join VS JOIN (Not looking for a religious battle)

[fnairb]

I have done some poking around (searching) and I have not found any definitive, authoritative, non-opinion answer to this.  Hopefully I can find it here.

 

Is there a technical/performance difference between...

SELECT ... FROM t1, t2 WHERE t1.x = t2.y

and...

SELECT ... FROM t1 JOIN t2 ON t1.x = t2.y;

[Barand]

I just ran a benchmark using a query which returns 11,000 rows, running each query 100 times. Differences were negligible

 

Explicit time : 30.2507 secs

Implicit time : 30.3697 secs

 

From a readability/maintenance viewpoint, the explicit version separates the structure from the selection criteria, making it easier to understand. It doesn't make much difference with your examples but when you are joining half a dozen tables with complex selection criteria then it certainly does.

 

From a consistency viewpoint, LEFT JOIN requires the explicit syntax, so why be different with INNER JOIN?

[fenway]

Agreed... I believe this may have been spawned by my post to another thread.

[fnairb]

@fenway:  This tread is indeed related to a post that you made fenway.  However, it has been a question I have had stewing for quite a while.

 

@Barand:  Thanks for the eye opener.

 

@all:

 

Barand's post supports my understanding about the performance differential being insignificant for a simple a simple join. 

 

However, Barand broke my comfort with exclusively using implicit joins by pointing out the separation of structure from criteria.  That was a point of view that I had never considered before.  It was a combination of an "Ahh haaaa!!!" and "Man, I'm an idiot!" moments.  The benefits of using JOIN all of a sudden made sense and I felt like an idiot for not figuring it out for myself.