LEFT JOIN in SQL Server (examples included)

You can use a LEFT JOIN in SQL Server to get all the records from the left table, and the records from the right table where there is a match (if there is no match, you’ll get NULL under the selected fields from the right table):

SELECT 
table_1.*,
table_2.*
FROM [database_name].[table_schema].[left_table_name] table_1
LEFT JOIN [database_name].[table_schema].[right_table_name] table_2 ON table_1.id = table_2.id

Let’s see how to apply a left join by reviewing few examples.

Applying Left Join Between Two Tables in SQL Server

In the following example, you’ll see how to apply a left join between:

  • The left table called PersonName (with a dbo schema); and
  • The right table called PersonAge (also with a dbo schema)

The records under the PersonName table are:

Example of a table

While the records under the PersonAge table are:

Example of a dataset

The Age_ID field (which exists in both tables) will be used to link the tables together.

Here is the complete query to perform the left join:

SELECT
pr.*,
ag.*
FROM [TestDatabase].[dbo].[PersonName] pr
LEFT JOIN [TestDatabase].[dbo].[PersonAge] ag ON pr.Age_ID = ag.Age_ID

In green, you’ll get all the records and fields from the PersonName table (the “left” table), and in blue you’ll get all the records and fields from the PersonAge table (the “right” table):

LEFT JOIN in SQL Server

Notice that there is one record (for the Person_ID = 333) with NULL values under the PersonAge table. This happened because the Age_ID of 1015 was not present in the right PersonAge table.

Selecting subset of fields

At times, you may need to select a subset of fields from the joined tables.
For example, let’s select:

  • The First_Name and the Last_Name fields from the PersonName table; and
  • The Age field from the PersonAge table

Here is the query:

SELECT
pr.First_Name,
pr.Last_Name,
ag.Age
FROM [TestDatabase].[dbo].[PersonName] pr
LEFT JOIN [TestDatabase].[dbo].[PersonAge] ag ON pr.Age_ID = ag.Age_ID

You’ll now see only the selected fields from the tables:

LEFT JOIN in SQL Server

Applying Left Join Across Multiple Tables in SQL Server

What if you want to perform a left join across multiple tables?

For example, let’s add a third table called Cities:

Table in SQL

So the complete query would look as follows:

SELECT
pr.First_Name,
pr.Last_Name,
ag.Age,
ct.City_Name
FROM [TestDatabase].[dbo].[PersonName] pr
LEFT JOIN [TestDatabase].[dbo].[PersonAge] ag ON pr.Age_ID = ag.Age_ID
LEFT JOIN [TestDatabase].[dbo].[Cities] ct ON pr.City_ID = ct.City_ID

As you can see, the first two fields were selected from the PersonName table (in green), the third field was selected from the PersonAge table (in blue), while the forth field was selected from the Cities table (in orange):

LEFT JOIN in SQL Server

Another way to write the above query:

SELECT 
pr.First_Name,
pr.Last_Name,
ag.Age,
ct.City_Name
FROM

	(
	SELECT 
	* 
	FROM [TestDatabase].[dbo].[PersonName]
	) pr

LEFT JOIN

	((
	SELECT 
	* 
	FROM [TestDatabase].[dbo].[PersonAge]
	)) ag ON pr.Age_ID = ag.Age_ID

LEFT JOIN

	(((
	SELECT 
	* 
	FROM [TestDatabase].[dbo].[Cities]
	))) ct ON pr.City_ID = ct.City_ID

You’ll get the same results as before:

Left Join

And yet another way to write the above query:

SELECT 
mix.First_Name,
mix.Last_Name,
mix.Age,
ct.City_Name
FROM

	(
	SELECT 
	pr.First_Name,
	pr.Last_Name,
	pr.City_ID,
	ag.Age
	FROM [TestDatabase].[dbo].[PersonName] pr
	LEFT JOIN [TestDatabase].[dbo].[PersonAge] ag ON pr.Age_ID = ag.Age_ID
	) mix

LEFT JOIN

	(((
	SELECT 
	* 
	FROM [TestDatabase].[dbo].[Cities]
	))) ct ON mix.City_ID = ct.City_ID

You’ll see the same results again:

LEFT JOIN in SQL Server