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.
| Author |
Topic |
|
yogi86
Starting Member
13 Posts |
 Posted - 2011-06-22 : 13:59:42
|
| What's the difference between using:Innerjoin atable on btable.name = atable.namevsWhere atable.name = btable.name???This confuses me and seems to work the same? Or is this an illusion? |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-06-22 : 14:08:15
|
| 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. |
 |
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2011-06-23 : 00:17:29
|
| 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 PublishingAnalytics and OLAP in SQLData and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-06-23 : 07:43:46
|
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. |
|
|
|
sqlnub
Starting Member
1 Post |
Posted - 2011-06-23 : 13:07:11
|
| 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. |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2011-06-23 : 13:47:53
|
| 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 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
mmarovic
Aged Yak Warrior
518 Posts |
Posted - 2011-06-24 : 11:43:36
|
| 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.MirkoMy blog: http://mirko-marovic-eng.blogspot.com/ |
|
|
|
|
|
|
|
|
Inner Join vs Where