[thelist] SQL question

Paul Cowan evolt at funkwit.com
Tue Dec 10 17:47:01 CST 2002


Carl J Meyer wrote:
> So my (possibly stupid) question is: why use the JOIN keyword?  What's
> the advantage over a simple WHERE clause join?  Is it just a matter of
> clearer syntax (for my money, the JOIN syntax isn't clearer)?

Short answer part (i): yes, it's _mostly_ purely syntactical.

> Or is there some performance difference that I don't know about?

Short answer part (ii): no, there shouldn't be. Not with a reasonably
intelligent DB, anyway.


> Or are there things you can do with JOIN that you can't do with
> the WHERE clause?

Now, we get to the long answer. Yes, there are things that you can't
do. Certainly not easily -- not without using subqueries or other
tomfoolery.

For an excellent (if long) example, see Soukup and Delaney's fantastic
book, Inside Microsoft SQL Server 7.0 (I assume the SQL Server 2000 version
would be just as good), Chapter 7.

Someone has a copy of the section in question (legally or not, I don't
know) at:
http://www.bamdad.org/~hamid/books/eee.geo.co.il/book/Misc/SQLServer7.0/ch07
c.htm
(watch for wrap)
but it's slow, so try the google cache at
http://216.239.39.100/search?q=cache:Vzmx_21ESDIC:www.bamdad.org/~hamid/book
s/eee.geo.co.il/book/Misc/SQLServer7.0/ch07c.htm
(definitely watch for wrap)
under the heading "The Obsolete *= OUTER JOIN Operator" for a
counter-example against the "old-style" joins.


Hope this helps.

Paul.




More information about the thelist mailing list