T-SQL left joins with SQL Server

With T-SQL Server left joins, it is possible to extract data from two or more SQL tables based on the relationships between the objects. MS SQL left joins indicate how the SQL engine should use data from one table to select rows from another table.

A join can group data from several tables, but each relationship is between two tables in the query. The query conditions indicate how to logically relate data between two tables.

Thus, join conditions can be specified in the FROM clause or the WHERE clause. When using the FROM clause, it is the relationship that filters the data displayed.

SQL Server left joins examples

First, the goal of using joins is to avoid storing the same data multiple times in a database. This is called normalization of a relational database. For example, you have 2 million of lines of sales per year but only 10 different physical stores and 20,000 customers in the database.

It’s better to store all the stores and customers information just once in a dedicated table. Hence you reduce the database size. And you also increase the performance.

What is a T-SQL join query ?

A join query is a selection of data from two or more tables, linked with a join condition to filter the data. It is usually filtered using a primary key in the first table. And a foreign key in the other tables. This ensures the coherence of the data in the database. It is logical keys that are used, and it is not mandatory that physical constraints are present in the data model.

How to join data from two tables in SQL Server ?

To join two tables in SQL Server, it is quite straight forward. Simply specify the columns to display from both tables. Then use the JOIN condition in the FROM clause and the filter condition in the WHERE clause.

What are the different types of T-SQL joins available with SQL Server ? The different types of joins in T-SQL are the following:

  • Left join
  • Left outer join
  • Right join
  • Right outer join
  • Full join
  • Full outer join
  • Inner join

Here we’ll be focusing on the left joins.

To start illustrating the different types of joins with queries examples, we need some sample data. Let’s use the AdventureWorks database, as I’m using the MS SQL 2019 version, I downloaded the [AdventureWorks2019] db. All steps explained here with the links to all AdventureWorks versions, from SQL Server 2008 R2 to SQL Server 2019.

Let’s consider the Person table and the Store table.

-- The sales person table
SELECT TOP 15
	[BusinessEntityID], 
	[TerritoryID], 
	[SalesQuota], 
	[Bonus], 
	[CommissionPct], 
	[SalesYTD], 
	[SalesLastYear], 
	[rowguid], 
	[ModifiedDate]
FROM [Sales].[SalesPerson];

-- The store table
SELECT TOP 15
	[BusinessEntityID], 
	[Name], 
	[SalesPersonID], 
	[rowguid], 
	[ModifiedDate]
FROM [Sales].[Store] 
ORDER BY [SalesPersonID];

The salesperson and the store tables are linked together with a physical constraint.

Please note that the SalesPersonID from the [Sales].[Store] column is linked to the BusinessEntityID in the [Sales].[SalesPerson] table. That difference in the names of the columns is confusing.

Here an extract of how the two tables, the code is from the [Sales].[Store] create table statement:

ADD  CONSTRAINT [FK_Store_SalesPerson_SalesPersonID] FOREIGN KEY([SalesPersonID]) REFERENCES [Sales].[SalesPerson] ([BusinessEntityID])

The salesperson and the sales territory tables are also linked. Script extract from the [Sales].[SalesPerson] code:

ADD  CONSTRAINT [FK_SalesPerson_SalesTerritory_TerritoryID] FOREIGN KEY([TerritoryID]) REFERENCES [Sales].[SalesTerritory] ([TerritoryID])

SQL Server left join query example ?

For example, to display the store name and the sales information of the salesperson, we must join the SalesPerson table and the store table. Note that you must select an alias for the tables to make the query readable.

The left join is called like because the first table in the FROM clause is considered the first table. So, it joins the data from the left table with data from the right table.

SELECT 
	person.[BusinessEntityID],
	store.[Name] as StoreName, 
	[TerritoryID], 
	[SalesQuota], 
	[Bonus], 
	[CommissionPct], 
	[SalesYTD], 
	[SalesLastYear], 
	person.[rowguid], 
	person.[ModifiedDate]
FROM [Sales].[SalesPerson] person
	LEFT JOIN [Sales].[Store] store 
		on person.BusinessEntityID = store.[SalesPersonID]
ORDER BY person.[BusinessEntityID];

MS SQL left outer join query example (left join vs left outer join)

The LEFT JOIN and the LEFT OUTER JOIN are the same, because they return the exact same set of data. To remove the data only available in the right table, use the WHERE clause and exclude the NULL values. As in the example below.

SELECT 
	person.[BusinessEntityID],
	store.[Name] as StoreName, 
	[TerritoryID], 
	[SalesQuota], 
	[Bonus], 
	[CommissionPct], 
	[SalesYTD], 
	[SalesLastYear], 
	person.[rowguid], 
	person.[ModifiedDate]
FROM [Sales].[SalesPerson] person
	LEFT JOIN [Sales].[Store] store 
		on person.BusinessEntityID = store.[SalesPersonID]
WHERE store.[SalesPersonID] IS NOT NULL
ORDER BY person.[BusinessEntityID];

How to manage SQL Server join performance

To control and maintain a good performance and response time to join queries, make sur to limit the data in the FROM statement.

For example, limit the number of rows to be scanned as soon as possible in the process. For example by adding a filter in the left table.

This article exposes a few queries example on how to build MS SQL left joins.