difference between table joining ways [duplicate]

Question

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;

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
– Damien_The_Unbeliever, Commented Jan 9, 2013 at 11:41

Neville Kuyt · Accepted Answer · 2013-01-09 11:12:51Z

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!

answered Jan 9, 2013 at 11:12

Neville Kuyt

29.6k2 gold badges40 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

vidyadhar · Accepted Answer · 2013-01-09 11:55:32Z

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

answered Jan 9, 2013 at 11:55

vidyadhar

3,1787 gold badges25 silver badges32 bronze badges

2 Comments

Damien_The_Unbeliever Over a year ago

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.

vidyadhar Over a year ago

if both the columns in the on clause indexed it go pick data with rapid speed

Collectives™ on Stack Overflow

difference between table joining ways [duplicate]

2 Answers 2

Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Linked

Related