The topic this month is joins, in honor of Sebastian’s a-join-a-day series. He’s writing about various aspects of joins, and invites us all to do the same thing on this Tuesday.

Writing Better Joins

I’m not a T-SQL expert. I can write code, and understand many type of queries, but I’m not one to dazzle others with their code, like Jeff Moden can. Instead, I want to talk about how I’ve learned to ensure that my code makes sense, is understandable, and most importantly, easy to find the mistakes inside.

I mainly do this by paying attention to the formatting of the code. I would say that once I started to get away from writing code like this, I found bugs easier, and understood the code better:

That actually came from reformatting the code using SQL Prompt, a product from my employer, Red Gate Software. I’m lucky in that SQL Prompt formats things as I’d prefer them, indenting and getting the JOIN and ON clauses onto separate lines.

Having code with structure, where you can clearly see the tables being joined, the clauses in use, and not miss any of the columns being selected at a glance is important. When you’re under stress and trying to debug or develop something, it’s easy to miss something that’s happening in the code if it’s not formatted correctly.

Whether you like commas before or after columns, or you want things indented so that the names of objects line up doesn’t really matter. What’s important is that you and your team agree on a set of formatting, or have tools that reformat things for each developer in a consistent way. You’ll spend less time trying to understand the code and more time building or fixing it, if it has a consistent layout.