Differens between join and comparing field using where

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Differens between join and comparing field using where

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-07-19 : 08:33:47

Jenny writes "Hi!
I have "inherited" an application from a coworker where the sql statements are something like this

Select akt.avskod, handl.namn
from akt, handl
where akt.handlid = handl.handlid

I was taught to use joins and would use

Select akt.avskod, handl.namn
from akt inner join handl
on akt.handlid = handl.handlid

What is the difference? Is there a performance difference? Should I change to my way of writing statements or hang on to the original way?

Thanks in advance

Jenny"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-19 : 08:38:12

You can use the WHERE syntax, but the ANSI standard for SQL defines the JOIN syntax as the preferred method. In SQL Server, the JOIN clause is processed separately from the WHERE clause, and will most likely optimize better than a WHERE clause-type join. There are lots of instances in which the WHERE syntax leads to goofy results, especially when outer joins are involved. The *= outer join syntax is marked for death, use LEFT JOIN instead.

I'd recommend changing them; even though the WHERE syntax will probably still be viable for a long time, it *could* be phased out of the ANSI SQL standard.

macka
Posting Yak Master

162 Posts

Posted - 2002-07-19 : 08:38:24

You are correct to use the inner join syntax. This is the ANSI standard. Check out this link for more info:

[url]http://www.swynk.com/friends/boyle/ansijoins.asp[/url]

macka.

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2002-07-19 : 15:47:20

Robvolk said:

You can use the WHERE syntax, but the ANSI standard for SQL defines the JOIN syntax as the preferred method.

It does not. There is no such notion as a preferred method in the ANSI standard document.

It may be true that one or the another is better optimized in some dbms (as they are equivivalent, this should not really need to be the case).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources