This 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.