0

Possible Duplicate:
SQL join: where clause vs. on clause

What are the difference between of following table joining ways and which one is more appreciate for query optimization.

 SELECT SP.*, S.SNAME, S.STATUS, S.CITY
 FROM S INNER JOIN SP 
 ON S.SNO = SP.SNO;

and

 SELECT SP.*, S.SNAME, S.STATUS, S.CITY
 FROM S, SP 
 WHERE S.SNO = SP.SNO;
Community
  • 1
  • 1
AKZap
  • 1,181
  • 6
  • 17
  • 31
  • As soon as you want to use `OUTER` joins, you should be using the first style, and then for consistency, you should probably use the same style for `INNER` joins. – Damien_The_Unbeliever Jan 09 '13 at 11:41

2 Answers2

1

The query optimiser will treat them both as equivalent - there is no difference in performance on all the major database engines.

From a readability and stylistic point of view, using the explicit join syntax is usually preferred - whatever you do, settle on a convention and stick to it!

Neville Kuyt
  • 29,247
  • 1
  • 37
  • 52
0

there is one point to remember here

the column on which index is defined should be the first in on clause make a little difference

vidyadhar
  • 3,118
  • 6
  • 22
  • 31
  • 2
    That is not generally true. We don't know what RDBMS the OP is using, but any one worth its salt will be able to rearrange predicates to evaluate them in the most efficient manner. – Damien_The_Unbeliever Jan 09 '13 at 12:05
  • if both the columns in the on clause indexed it go pick data with rapid speed – vidyadhar Jan 09 '13 at 12:25