SQL Server left join examples

10 March 2022

With this tutorials on SQL Server left join examples, it is possible to extract data from two or more SQL tables. The query is 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.

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 SQL Server left join query ?

A SQL Server left join query is a way of selecting data from two or more tables by linking them using a join condition to filter the data. It is called a left join because the first table in the FROM clause is considered the left table, and it joins the data from the left table with data from the right table. Typically, a primary key in the first table and a foreign key in the other tables are used to ensure data coherence in the database. However, physical constraints are not mandatory, and logical keys can be used.

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 sample queries

To illustrate a SQL Server left join query, suppose we want to display the store name and sales information of the salesperson. To do this, we need to join the SalesPerson table and the Store table using a left join. It is important to select an alias for the tables to make the query readable. The left join is called like this because 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)

In MS SQL, 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, you can use the WHERE clause to exclude the NULL values. For instance, you can use a query like the one below to remove the NULL values and retrieve the data only available in the left 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]
WHERE store.[SalesPersonID] IS NOT NULL
ORDER BY person.[BusinessEntityID];

How to better manage T-SQL joins performance ?

To ensure optimal performance and response time for join queries, it is essential to limit the data in the FROM statement. For instance, you can limit the number of rows to be scanned by adding a filter to the left table. This will reduce the amount of data that needs to be processed. The tutorial provides several SQL Server left join examples that can be used to build MS SQL database applications.