XML FeedsThis month’s T-SQL Tuesday is hosted by Sebastian Meine (blog | twitter) and the topic is joins. He has a whole month worth of topics about joins: A Join A Day – Introduction.
My blog post is about something weird I encountered while I was converting some old Oracle code to T-SQL (I encounter all sorts of weird stuff in Oracle code, but let’s stay on topic). Amongst all the usual cursor-madness, I stumbled upon this query:
- SELECT mt1.Column1, mt2.Column2
- FROM [dbo].[MyTable1] mt1, [dbo].[MyTable2] mt2
- WHERE mt1.[JoinColumn1] (+)= mt2.[JoinColumn1]
- AND mt1.[JoinColumn2] (+)= mt2.[JoinColumn2]
The original query was much longer and more complex, but I kept only the necessary parts. I recognised the old-style of writing joins, also known as ANSI-89 joins (read more about them in Bad habits to kick : using old-style JOINs). However, I never came across the type of join designated by a (+)=. Most likely this has something to do what my lack of experience with Oracle, which I am very proud of 
. SQL Server didn’t recognise the syntax so it had to be either Oracle only, or something very outdated. I asked about this on Twitter and a few people came up with compound operators which were introduced in SQL Server 2008, but luckily Mladen Prajdic (blog | twitter) vaguely remembered those could be LEFT OUTER JOINs.
* UPDATE: Apparently this syntax coresponds with RIGHT OUTER JOIN. For more info, see Robs comment below *
And he was right. After some Googling I found this Oracle doc page: Joins. It is described as the “Oracle join operator”. They advise not to use it, but you know, stuff like this always pops up in legacy code.
So, if you’re converting some old Oracle code (we have to get rid of it once, don’t we?), now you know how to deal with this monstrosity.
Koen Verbeeck is a Microsoft Business Intelligence consultant at Ordina Belgium, helping clients to get insight in their data. Koen has a comprehensive knowledge of the SQL Server BI stack, with a particular love for Integration Services. He's also a speaker at various conferences.
Nice post, I must say that I rarely see *= or =* (the old style SQL Server outer joins) anymore. I did mention it in the 
Interesting, I was wondering if SQL Server had an equivalent syntax to (+)=. Good to know if I ever have to update legacy SQL Server code :) 
Actually, if the (+) appeared before the = that made it a RIGHT outer join. The plus was along the lines of "plus nulls".
What's certainly clear from this post, comments inclusive, is that this is some really confusing syntax. Glad to see that *= is gone as well! :)
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.