Multi-Joins In SQL

Kevin Feasel



Vladimir Oselsky hits on something I dislike:

My first gut reaction was that this code is broken and would not run. To my amazement code ran just fine. Now came the hard part, which was to figure out what the code was doing because I have never seen this syntax before. Since I did not understand what I was looking at I could not BING “weird join syntax” to get an answer. As a developer, I learned long time ago to break down code into smallest possible chunks to get the answer.

After I have figured out the relationship between tables, I was able to understand what query was doing. To be able to read query better it can be rewritten in the following way.

Do read Vlad’s post.  I’ve seen terrible misuse of this plus right outer joins, and I don’t know if I’ve ever seen a case where using this syntax made the code easier to understand.

Related Posts

ISNUMERIC And Unexpected Results

Jen Stirrup explains why ISNUMERIC isn’t all that great: I noted that one of the columns failed to convert VARCHAR to DECIMAL. The error message is below, and it’s usually fairly easy to sort:Error converting data type varchar to numeric Normally, I’d use ISNUMERIC to identify the rows that fail to have a value in that column that could be […]

Read More

sp_executesql WITH RECOMPILE

Erik Darling points out that running sp_executesql with the WITH RECOMPILE setting doesn’t really change anything: This’ll give us the key lookup plan you see above. If I re-run the query and use the 2010-12-30 date, we’ll re-use the key lookup plan. That’s an example of how parameters are sniffed. Sometimes, that’s not a good thing. […]

Read More


March 2017
« Feb Apr »