SQL Server Join Syntax: It’s Changed!

Alright so maybe that was a little misleading of a title… SQL Server join syntax changed awhile ago, when ANSI-92 joins became the norm. And Microsoft was threatening to remove it for awhile. The older outer join syntax just doesn’t work in SQL Server 2012. 

We All Knew SQL Server Joins Were Changing…

I know… I’m not really telling a lot of folks anything new here, am I? Most of us have been writing INNER JOIN and LEFT OUTER JOIN for a long while now. Not everyone got the memo though and a quick little funny story that I experienced with a client of mine is a good reminder of that But first..

Review on SQL Join Syntax Evolution

It used to be that you would just list your tables in the from clause separated by a comma and join the tables via the where clause:

SELECT A.Column1, B.Column1

FROM TableName1 AS A, TableName2 AS B

WHERE A.IDColumn = B.IDColumn

That joins TableName1 and TableName2 on any rows that have a match in the ID column. If we remember back to our SQL Server join 101 – that means that we are doing an inner join. The rows that satisfy the condition are returned – they have to be in each table. To get all the rows from one table regardless of whether they have a match and then return the info from other tables if they have a match, we do an outer join.. A left outer join gives us the rows in the left hand table whether there is a match or not with the rows in the right hand table. If there is a match, get get that second tables requested rows as well. If not, we get a NULL. The “old” way of doing this:

SELECT A.Column1, B.Column1

FROM TableName1 AS A, TableName2 AS B

WHERE A.IDColumn *= B.IDColumn

When the ANSI-92 standard came out, the “JOIN” word was added to the lexicon and it eventually started circulating through the database products out there. Instead of a *= or += in a WHERE clause, you use the right word and just tell your DBMS what you are joining on via the ON clause.. The two examples above respectively become:

SELECT A.Column1, B.Column1

FROM TableName1 AS A

INNER JOIN TableName2 AS B

ON A.IDColumn *= B.IDColumn

AND

SELECT A.Column1, B.Column1

FROM TableName1 AS A

LEFT OUTER JOIN TableName2 AS B

ON A.IDColumn *= B.IDColumn

Alright… So I probably just bored 95% of you reading this because you either already knew about this change or never even knew about the “old way” of doing joins. You can still do your inner joins this way (I don’t recommend it for the reasons below) but you cannot do your outer joins this way in SQL Server 2012.

A Story About Joins…

I don’t share this to poke fun at anyone. It is a real story that happened to a real client of mine and a real DBA at a real client of theirs. No names or industries will be shared here. It is humorous, and it may be one of the funniest exchanges I’ve been a part of this year on the job. (It was only funny because it was very quickly rectified and no reports were harmed in the process)..

So.. I had a client I’ve helped a couple times with some consulting work. Great customer. Great product and service. They host for the vast majority of their clients and offer their product as a service. A couple customers, though, self host. I was engaged by them to help with some SQL tuning and a sanity check to help their product perform the best it could. During the process of that check, I noticed they occasionally used some of the older style outer joins that were deprecated and soon wouldn’t be supported. I included that on a list of some things to work through. It wasn’t the most critical, but I told them it affected readability of their code and the support of that syntax was waning. They agreed to fix that stuff and did end up fixing almost all instances of it, but they hadn’t done a thorough test on 2012 yet so they had a couple here or there they left hanging around..

Fast forward to this week..  They called up asking for some assistance and it was a client affecting issue so I told them I’d see what I could do. Turns out one of their self-hosted clients upgraded to SQL Server 2012. They were advised to thoroughly test and ensure all looked good because they hadn’t yet tested the app on SQL Server 2012. Well somehow the client ended up doing the upgrade and a couple reports started failing after they were live with success for a couple days on SQL Server 2012. They asked me to look and I quickly looked at the report definitions and found “WHERE column *= column” and said AHA! This JOIN syntax won’t work! And told them. They agreed and started working on changing those couple reports that were still using the old syntax.

I “Fixed” The Typos!

But… At the same time as I was finding the issue, they received an e-mail from their client’s DBA/Developer indicating they found and fixed the issue. The client DBAs assessment of the situation was [highlight]There was a typo in your code… I found some cases where an “=” sign was turned into a “*=”. I fixed the typo and turned those back into “=” signs and all works![/highlight]

Now. Because I knew we had the real solution and it would all be better soon, I felt that a quick bout of laughter in my office would be okay when I read that note. So I did. I mean.. It’s funny. If it happened to me and I was the DBA, I’d look back and think it’s funny, too. I mean.. Come on.. It’s funny. Those typo fixes were the same thing as rewriting “LEFT OUTER JOIN” as “INNER JOIN” – total behavior change and different results can definitely happen.

SQL Server Joins – Lessons From This…

Again – we rectified the situation, everyone is happy and everything is good with that situation. I was hit with a few lessons after that situation though. I think they are important (and I also think they help to excuse the DBA who honestly thought there was a typo):

1. There is a new generation out there – Not everyone started with these database systems when everyone did their joins in the WHERE clause. In fact there is a generation at work right now in our offices that have never seen one of these types of joins. Not in college, not in the field, not in a book.
2. Someone else maintains your code – Listen.. Your code isn’t self documenting. Comments aren’t useless. If you build something, build it so those coming after you can pick it up and work with it. Don’t be that developer. It may even be you that has to pick up your code later. Make it easier on the next person…
3. Pay attention to the deprecated lists – Microsoft maintains a list of these. Go through it, see what you are doing that isn’t in the long term game plan and adapt… They don’t just yank something out, they tend to give you 3 versions advanced notice! If you were planning on maintaining your code into the future, you shouldn’t be doing things that Microsoft gave you warning about removing. Now this client of mine didn’t know that and they were working to adjust but are you? 
4. Shotgun troubleshooting is tricky – Now I don’t fault this DBA too badly here. It would look like a typo to me if it highlighted in red squiggly lines and SSMS said “invalid syntax” and the query right above it had an “=” sign and it worked. But… I’m a big fan of accurate and informed troubleshooting when your data is on the line. I blog a lot about troubleshooting and this post talks about some steps. One of them is to research the solution and understand it.
5. The older left outer joins really don’t work anymore – That’s a good reminder.. Those *= joins don’t work in 2012. Whether you are in 2008 compatibility mode or not. Prepare for that if you haven’t already..
6. <shameless self promotion> If you are a vendor – have your “stuff” reviewed – One of the things we do well at Straight Path Solutions is help application vendors make the most of their product. We can make sure you are doing the best you can be and we’ll help find these potential issues before your clients call us and we help them with them. An independent set of eyes on your performance, maintenance recommendations and code can help me not write blog posts like this and instead write posts like this. </shameless self promotion>

So that’s it. SQL Server Join Syntax has changed, it was just awhile ago and it isn’t until now (SQL Server 2012) that the old style outer joins are no longer SQL Server Joins…