SQL inner join, which style is better?

Question

I know, if you want the inner join of two tables, you can write SQL in the following syntax.

select tableA.columnA 
from tableA 
   inner join tableB on tableA.columnB=tableB.columnB

Alternatively, you can write the following.

select tableA.columnA 
from tableA, 
     tableB 
where tableA.columnB=tableB.columnB

so, which is better in terms of performance?

They will be exactly the same on any decent SQL implementation. The explicit join syntax is usually preferred for clarity. — Blorgbeard, Jun 10 '13 at 20:49
I believe they're functionally equivalent, but the first is ANSI compliant whereas the second may be RDBMS-specific. — Darth Continent, Jun 10 '13 at 20:50
Also see http://stackoverflow.com/questions/334201/why-isnt-sql-ansi-92-standard-better-adopted-over-ansi-89 — Blorgbeard, Jun 10 '13 at 20:57
The first one is preferred because it states your intention clearer, and is less error prone when it comes to "accidental" cartesian joins. But you should never, ever mix those two styles. — a_horse_with_no_name, Jun 10 '13 at 20:57
Performance is what happens when you are figuring out what to do with the rest of your life. — wildplasser, Jun 10 '13 at 23:17

score 3 · Accepted Answer · answered Jun 10 '13 at 20:51

3

There is no difference in terms of performance. The where clause is in fact the same as INNER JOIN when it comes to relational algabra.

Read here for a brief explaination

answered Jun 10 '13 at 20:51

SpaceApple

1,248
1
22
45

score 0 · Answer 2 · answered Jun 10 '13 at 23:15

Make sure you understand how inner joins work though, inner join will return more records than you might expect if one of your tables contain duplicate records. So basically, for each record in table A, it will return all the matching records in table B, and if the next record in table A matches the same records in table B, they will appear again. Read more here.

SQL inner join, which style is better?

2 Answers2