When Adam Machanic started T-SQL Tuesday, I bet that nobody, least of all himself, expected it to run for over ten years. Yet, here we are. Not ten, but over 16 years later. Or 200 months, to be exact.

This anniversary edition is hosted by Brent Ozar. And his chose topic is: query red flags. Things that make you groan when you open a query and see them in the code. I’m sure there will be a ton of posts, because there are so many. I myself could probably fill a book with things I consider a red flag (and someone else would then point out that my queries have things that they consider red flags, but that is another discussion).

But let’s focus on just one thing in this post.

All the joins are LEFT

So you have a query. You need to join some tables. Three perhaps, or five, or sixteen. (By the way, please don’t join sixteen tables if you can avoid it!).

And then you write every join as a LEFT JOIN or LEFT OUTER JOIN. Each and every one of them.

When I see that, I shake my head, and I might even utter some words that are not fit for reproduction. Because I know this query is written by someone who fails basic understanding of their data model, of SQL, or (likely) both.

I then look a bit more at the query. There is a WHERE clause, on some of the columns from outer joined table. Which means they are not outer joined at all. The query optimizer will laugh at you behind your back, as it prepares an execution plan that simply does an inner join instead. Well, unless the query gets too complex. (Remember those 16-table joins??). Then, it might be beyond the optimizer, and you end up with an execution plan that wastes performance.

But there are still some left outer joins remaining. So now I look at the data, and the data model. I see a (trusted) foreign key between the tables. Once more, I know (or hope in the case of a too complex query) that the optimizer will have a good laugh at your expense and do an inner join instead, because the foreign key guarantees that there will always be a match.

Or I see an untrusted foreign key. Or no foreign key, but the data shows that there are always matches, and I find out that this is indeed guaranteed. But you, or someone else, decided that there was no need to tell SQL Server this. So SQL Server now has to create an execution plan that actually does an outer join. Because SQL Server does not know this critical information about your query. It is still wasted performance. There still will never be any difference with an inner join … except speed and resource usage. Oh, and also, fix your damn data model. Add that foreign key. Make sure it’s trusted. Stop making the optimizer work with one hand tied behind its back, make sure it has full information!

And yes. Sometimes, rarely, one of the outer joins survives my scrutiny. Sometimes, judging from the query, the constraints, and the data, it has to be there. Now there is still the question of whether it is actually needed. What is the business requirement? If the outer joined rows are not actually needed? Then the left join actually makes the query return incorrect results!

After all this, I am left with only a small amount of LEFT [OUTER] JOINs. Ones that are functionally required, and needed because of the query, the data, and the constraints. Those are the ones that should be there, even though they might make the execution plan a bit more expensive.

In real world queries, outer joins are rare. They happen. But they should be the exception, not the rule.

So what does this tell me about you?

When I see a query that has all LEFT OUTER JOINs, I instantly know something about you. Or about whoever wrote that query.

I know that you don’t understand the difference between outer and inner join. Or that you simply don’t care, which is even worse.

I know that you do not know the data model of your database. Or that you simply don’t care, which is far, far worse.

And I know that you don’t understand the performance impact. Or don’t care, which, again, is even worse.

You probably copied someone else’s pattern, without thinking. “Yeah, I always do it that way. One day I had a wrong query result because the INNER JOIN left out some rows, so now I always use LEFT OUTER JOIN.”

That, dear reader, is the equivalent of someone who nearly got in a car crash after driving through a red traffic light, and now always stops before every traffic light, regardless of the color. Don’t be that driver. And also don’t be any other driver who sees this and thinks it’s a great idea, that should be followed.