SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Inner Join vs Where
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

yogi86
Starting Member

USA
13 Posts

Posted - 06/22/2011 :  13:59:42  Show Profile  Reply with Quote
What's the difference between using:
Innerjoin atable on btable.name = atable.name

vs

Where atable.name = btable.name

???

This confuses me and seems to work the same? Or is this an illusion?

robvolk
Most Valuable Yak

USA
15654 Posts

Posted - 06/22/2011 :  14:08:15  Show Profile  Visit robvolk's Homepage  Reply with Quote
They work the same in SQL Server, but may not work the same in other database products. The query optimizer will likely generate the same plan regardless of which one you use. The OUTER JOIN style however does not work the same way with the equivalent WHERE syntax.

I've always preferred INNER JOIN since it clearly separates the criteria for joining tables from WHERE conditions. And it's been around long enough to be supported in almost every RDBMS product, so not using it is laziness/ignorance IMHO.
Go to Top of Page

jcelko
Esteemed SQL Purist

USA
547 Posts

Posted - 06/23/2011 :  00:17:29  Show Profile  Visit jcelko's Homepage  Reply with Quote
I had posted a history of the infixed notation from my ANSI X3H2 days and I don't feel like looking for it right now. The difference is "syntactic sugar" and they will both compile to the same plan. We had to define the [LEFT | FULL | RIGHT] OUTER JOIN as an infixed operator for mathematical reasons. It then became easy to define INNER JOIN, OUTER UNION and a bunch of other features in the same paper. So we did! Committee are like that ..

Weaker SQL programmers use INNER JOIN, since it is sequential and a familiar, procedural infix binary operator. Stronger SQL programmers use the WHERE syntax because it is set-oriented, obeys the law of proximity and is an n-ary operator. Again, I have the details but basically, it is the mindset of someone who write with a + and someone who writes with a Capital Sigma when they add sets of values.


--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Go to Top of Page

robvolk
Most Valuable Yak

USA
15654 Posts

Posted - 06/23/2011 :  07:43:46  Show Profile  Visit robvolk's Homepage  Reply with Quote
quote:
Originally posted by jcelko

I had posted a history of the infixed notation from my ANSI X3H2 days and I don't feel like looking for it right now. The difference is "syntactic sugar" and they will both compile to the same plan. We had to define the [LEFT | FULL | RIGHT] OUTER JOIN as an infixed operator for mathematical reasons. It then became easy to define INNER JOIN, OUTER UNION and a bunch of other features in the same paper. So we did! Committee are like that ..

Weaker SQL programmers use INNER JOIN, since it is sequential and a familiar, procedural infix binary operator. Stronger SQL programmers use the WHERE syntax because it is set-oriented, obeys the law of proximity and is an n-ary operator. Again, I have the details but basically, it is the mindset of someone who write with a + and someone who writes with a Capital Sigma when they add sets of values.
I guess the weakest SQL programmers sit on committees and come up with syntactic sugar.
Go to Top of Page

sqlnub
Starting Member

1 Posts

Posted - 06/23/2011 :  13:07:11  Show Profile  Reply with Quote
Ouch, I just joined this site today and was looking around. I am pretty new to SQL Server, I have only been working with it for a couple of years. I was excited thinking about trying to contribute on here as I have heard a lot of people say that contributing on forums helps build your knowledge. I have to admit that I am now very skeptible about doing so after reading Celko's comments. To degrade another member and imply that they are "weak" is RUDE. I don't care how many books you have written sir, it is people like me who buy those books that support you and your publisher. I hope you personally know this Volk guy and ya'll have some feud going otherwise sir, you are just mean and rude. I expect this kind of BS on stackoverflow regarding VB, C#, Pearl, etc, but not on a SQL Server forum, I thought you all had more class. Well, I guess some do and a few don't.
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 06/23/2011 :  13:47:53  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
Nah. No feud (at least that I know of).

jcelko is just like that. All the time. On any forum I've found him on.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

mmarovic
Aged Yak Warrior

Czech Republic
518 Posts

Posted - 06/24/2011 :  11:43:36  Show Profile  Visit mmarovic's Homepage  Send mmarovic a Yahoo! Message  Reply with Quote
I always prefered ansi join syntax. I found it more readable.

@sqlnub: Just ignore Joe and you'll be fine. I haven't seen such behavior from any other member of any sql forum and I had been active on several for years.

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000