It’s time once again for T-SQL Tuesday, and this month is hosted by SQLity.net, Sebastian Meine.
If you want to know more or participate, read the invitation and write your own blog post.
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:
WITH [EMP_cte]([BusinessEntityID], [OrganizationNode], [FirstName], [LastName], [RecursionLevel]) -- CTE name and columns
AS (
SELECT e.[BusinessEntityID], e.[OrganizationNode], p.[FirstName], p.[LastName], 0 -- Get the initial list of Employees for Manager n
FROM [HumanResources].[Employee] e INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = e.[BusinessEntityID]
WHERE e.[BusinessEntityID] = @BusinessEntityID
UNION ALL SELECT e.[BusinessEntityID], e.[OrganizationNode], p.[FirstName], p.[LastName], [RecursionLevel] + 1 -- Join recursive member to anchor
FROM [HumanResources].[Employee] e INNER JOIN [EMP_cte] ON e.[OrganizationNode].GetAncestor(1) = [EMP_cte].[OrganizationNode]
INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = e.[BusinessEntityID]
)
SELECT [EMP_cte].[RecursionLevel], [EMP_cte].[OrganizationNode].ToString() as [OrganizationNode], p.[FirstName] AS 'ManagerFirstName', p.[LastName] AS 'ManagerLastName',
[EMP_cte].[BusinessEntityID], [EMP_cte].[FirstName], [EMP_cte].[LastName] -- Outer select from the CTE
FROM [EMP_cte] INNER JOIN [HumanResources].[Employee] e ON [EMP_cte].[OrganizationNode].GetAncestor(1) = e.[OrganizationNode]
INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = e.[BusinessEntityID]
ORDER BY [RecursionLevel], [EMP_cte].[OrganizationNode].ToString()
OPTION (MAXRECURSION 25)
I often find code in forums, or sent to me and I need to reformat it so that it looks better. I prefer something like this:
WITH [EMP_cte] ( [BusinessEntityID], [OrganizationNode], [FirstName], [LastName], [RecursionLevel] )
-- CTE name and columns
AS (
SELECT
e.[BusinessEntityID]
,e.[OrganizationNode]
,p.[FirstName]
,p.[LastName]
,0 -- Get the initial list of Employees for Manager n
FROM
[HumanResources].[Employee] e
INNER JOIN [Person].[Person] p
ON p.[BusinessEntityID] = e.[BusinessEntityID]
WHERE
e.[BusinessEntityID] = @BusinessEntityID
UNION ALL
SELECT
e.[BusinessEntityID]
,e.[OrganizationNode]
,p.[FirstName]
,p.[LastName]
,[RecursionLevel] + 1 -- Join recursive member to anchor
FROM
[HumanResources].[Employee] e
INNER JOIN [EMP_cte]
ON e.[OrganizationNode].GetAncestor(1) = [EMP_cte].[OrganizationNode]
INNER JOIN [Person].[Person] p
ON p.[BusinessEntityID] = e.[BusinessEntityID]
)
SELECT
[EMP_cte].[RecursionLevel]
, [EMP_cte].[OrganizationNode].ToString() AS [OrganizationNode]
, p.[FirstName] AS 'ManagerFirstName'
, p.[LastName] AS 'ManagerLastName'
, [EMP_cte].[BusinessEntityID]
, [EMP_cte].[FirstName]
, [EMP_cte].[LastName] -- Outer select from the CTE
FROM
[EMP_cte]
INNER JOIN [HumanResources].[Employee] e
ON [EMP_cte].[OrganizationNode].GetAncestor(1) = e.[OrganizationNode]
INNER JOIN [Person].[Person] p
ON p.[BusinessEntityID] = e.[BusinessEntityID]
ORDER BY
[RecursionLevel]
, [EMP_cte].[OrganizationNode].ToString()
OPTION
( MAXRECURSION 25 )
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.
