LEFT JOIN in SQL Server: The Ultimate Guide for Beginners

The SQL Server LEFT JOIN is one of the most useful tools we have when querying SQL Server database. If you plan on working with Microsoft SQL Server databases for more than 10 minutes, you should expect to encounter a LEFT JOIN early in your career. It’s very important to know what the LEFT JOIN is and how it works.

(By the way, the LEFT JOIN is the same thing as the LEFT OUTER JOIN. The word ‘OUTER‘ is optional!)

The LEFT JOIN is part of the Top 6 tools you should know for writing AWESOME Select statements

If you want to learn more about other extremely common and useful query tools, check out that tutorial.

In this tutorial, we will be discussing the following topics:

1. What is the LEFT JOIN?
2. How is the LEFT JOIN different from an INNER JOIN?
3. What is the syntax for the LEFT JOIN?
4. How can the LEFT JOIN help us?
5. Are there different kinds of JOINs?
6. Tips, tricks, and links

The LEFT JOIN is discussed in the following FREE Ebook:

FREE Ebook on SQL Server JOIN Operations!

This guide contains all the key points you need to understand about the different JOIN operations in Microsoft SQL Server. It will definitely be a great resource to keep and reference throughout your career as a data professional. Download it today!

Let’s take it from the top:

1. What is the LEFT JOIN?

A LEFT JOIN is used in a SELECT statement to give you access to columns in two or more tables. It will return column data for all rows in the first table of your JOIN (aka the ‘left‘  table) and only column data for matching rows in the second table (aka the ‘right‘ table).   

I need to offer this piece of advise: If you don’t know how a JOIN works in the first place, you need to understand that first. I have a great article about INNER JOINs which also serves as an introduction tutorial to the whole idea of what a JOIN is and how it can help us. Check it out here:

SQL Server INNER JOIN: The Ultimate Guide for Beginners

Going back to the LEFT JOIN, we use this type of join when we specifically want to see all rows from the left side of our JOIN operator.

2. How is the LEFT JOIN different from an INNER JOIN?

As I said, the LEFT JOIN will return all rows from the left table in our JOIN operator.

This is different from an INNER JOIN which will only return a row from our left table if it has a matching row in the other table (aka the ‘right’ table).

So again, the LEFT JOIN will return all rows from the left table even if there isn’t a matching row in the right table.

I have to say it again: You need to understand how a JOIN works in the first place before you can understand how a LEFT JOIN works. See the tutorial here.

There is a helpful diagram you will likely encounter when understanding the LEFT JOIN. Here it is:

The LEFT JOIN will just pull all rows from the left table, regardless of if there is a matching row in the right table. But from the right table, we only pull rows that have a matching row in the left table.

Let’s think of a quick example. Let’s say you have a table called Products that has a ProdID column:

Then we have a table called Orders. In this table, each row represents a unique order that was placed for our business. In each row, we have a reference to a ProdID to identify what product was purchased:

So going down the list of ProdID’s in the Orders table, we can see in order # 1, the customer bought Product ID # 2, which appears to be a Small Bench. In order # 2, the customer purchased Product # 4, which appears to be Side Tables. You get the idea.

Here is a quick INNER JOIN to see the Product Names for all orders in our system:

But again, this is going to show us all products that have a matching ProdID between the Products and Orders tables.

What if we want to know what products haven’t been purchased by anyone? In that case, we would want to see all products, regardless of if there is a match in the Orders table.

In that case, in our result set, we would see a ProdID value and a ProdName value, but nothing in the OrderID column.

This is where the LEFT JOIN would come in handy.

Can you predict what the query will look like? (I’ll reveal the answer soon).

3. What is the syntax for the LEFT JOIN?

It’s very similar to an INNER JOIN. Here is the syntax for the LEFT JOIN:

SELECT <column list>
FROM <table one>
LEFT JOIN <table two>
ON <table one>.<column to match> = <table two>.<column to match>

As I mentioned earlier, you can add the word OUTER if you want to. It is more common to leave it out, though. 

Let’s walk through this briefly. First, we have the <column list>. Of course, this can contain columns from one or both tables (usually you have at least one column from each table).

The first table, <table one> is the left table. The table in your LEFT JOIN clause is the right table. 

This might seem opposite to what you would think. You might think the table in the LEFT JOIN clause would be the left table, but it isn’t. The table in the FROM clause is the left table.

Then we have the ON clause, where we identify how we are linking the two tables. Again, we are still pulling all rows from the left table, but this ON clause will figure out which rows to pull from the right table.

4. How can the LEFT JOIN help us?

Let’s go back to our example to learn how a LEFT JOIN can help us.

We want to know what products haven’t been purchased by anyone. In plain English, this would mean we want to see products that don’t have any Orders associated with them.

If we’re doing a JOIN between the Products table and the Orders table, we would want to display everything we have in the Products table, regardless of if there is a corresponding order in the Orders table.

A LEFT JOIN is perfect for that:

So again, we’re seeing everything we have in the Products table. If there is a match to the ProdID in the Orders table, we also show the OrderID of that order.

As we can see, there are several items that have been ordered more than once, like the Small Bench and the Side Tables.

But the most obvious thing we see is the OrderID for the “Calendar Rack” and the “Plaque“…..

THERE ISN’T ONE

When we do our LEFT JOIN, we pull everything we have from the left table, regardless of if there is a match in the right table. But in the scenario where there is NOT a match, what value are we supposed to use for columns from the right table?

Well, NULL represents the absence of information, which is exactly the situation we find ourselves in. Therefore, NULL is used as the value of OrderID.

Learn more about NULL here: SQL Server NULL: Are you making these 7 mistakes?

This is helpful towards answering our question of “What products have we never sold”? From the query above, it looks like the Calendar Rack and the Plaque have never been sold.

Great. But let’s think about how we can make this query ever better. Maybe we want to see only products we have never sold and nothing else.

In other words, we only want to see products that do not have a corresponding order. Well, that means we want to see products with an OrderID of NULL specifically:

JOIN operations can help us see meaningful information

Let’s think about another way to present this information. Maybe we want to know how many orders have been placed for each of our products.

We’re not interested in seeing the OrderID anymore. We just want to know how many orders have been placed for each product.

The COUNT() aggregate is great for that kind of information: 

We use the handy COUNT() aggregate to count the number of OrderID’s we have for each of our products. This query uses the the handy GROUP BY clause.

Learn more: GROUP BY clause: A How-To Guide

Again, we still show everything from the Products table, but the number of OrderID’s for unpurchased products will be ZERO. If we’re interested in seeing these unpurchased products first, we would use an ORDER BY clause as I have done. We would make sure to show the ‘Number of Orders‘ in ascending order, which presents the information from smallest to largest.

Pretty groovy, eh?

5. Are there different kinds of JOINs?

Yes. Here is the full list:

• INNER
• LEFT OUTER
• RIGHT OUTER
• FULL OUTER
• CROSS

I have already discussed how I have a full tutorial on the INNER JOIN. The tutorial also serves as a great introduction to JOINs and how they can help us. Check out that tutorial here:

SQL Server INNER JOIN Operator: The Ultimate Guide for Beginners

The logic of the RIGHT OUTER JOIN is identical to the LEFT OUTER JOIN, but it applies to the table on the right. Remember, the table in the FROM clause is the left table, and the table in the JOIN clause is the right table. 

Here is the RIGHT OUTER JOIN diagram:

So we pull everything we have from the right table, and only matching rows from the left table.

If you think about it, we could have achieved the same result in our SQL query if we used a RIGHT OUTER JOIN and just flipped the tables around:

Before:                                                                  After:

Notice all I did was flip the tables around and specify a RIGHT JOIN, and we got the same results!

Learn more about the RIGHT JOIN: RIGHT JOIN in SQL Server: Explained for Beginners

I’ll go ahead and explain a FULL OUTER JOIN, because it’s simple.

A FULL OUTER JOIN is sort-of a combination of the LEFT and RIGHT JOINs. The FULL OUTER JOIN pulls all rows from both tables. Period.

Here’s the diagram:

Here’s the example. I went ahead and created an order that references a product that doesn’t exist:

Order # 19 is the order that references a product that doesn’t exist, which makes the ProdID and ProdName columns NULL.

As we already know, the “Calendar Rack” and “Plaque” products have never been ordered, so the OrderID for those rows is also NULL.

Finally, I don’t see the CROSS JOIN much, but it’s good to know as well.

Learn more: CROSS JOIN in SQL Server: Explained for Beginners

6. Tips and tricks

Here is a list of some helpful tips and tricks you should know about the LEFT JOIN operation:

Tip # 1: The word ‘OUTER‘ is optional

As I have mentioned several times, you can leave out the word ‘OUTER‘ in your LEFT OUTER JOIN. There is no such thing as a LEFT INNER JOIN, so SQL understands what you mean when you just say ‘LEFT JOIN‘

Tip # 2: LEFT JOIN can be used instead of RIGHT JOIN

Remember, you really don’t need the RIGHT OUTER JOIN. You can just use a LEFT OUTER JOIN and flip your tables around.

Tip # 3: You can perform an UPDATE statement with a JOIN 

Normally, you would do a LEFT JOIN in a SELECT statement, but it’s also possible to run UPDATE statements with a JOIN to modify data. Check out the full tutorial here:

UPDATE statement with JOIN: How it’s done

Next Steps:

Leave a comment if you found this tutorial helpful!

If you found this tutorial helpful, make sure you download your FREE Ebook:

FREE Ebook on SQL Server JOIN Operations!

This guide contains all the key points you need to understand about the different JOIN operations in Microsoft SQL Server. It will definitely be a great resource to keep and reference throughout your career as a data professional. Download it today!

Be sure to check out my tutorial on the INNER JOIN. This tutorial also serves as the introduction to JOINs and why we need them. That tutorial contains several tips and tricks you should know when writing JOINs. Here’s the link:

SQL Server INNER JOIN: The Ultimate Guide for Beginners

Also, I put together a tutorial to answer some of the most beginner-level questions you might ask if you are just starting out with SQL Server and are trying to learn about JOINs. Here’s the link:

What’s the difference between JOIN and LEFT JOIN? This and other JOIN questions, answered!

Finally, the LEFT JOIN is part of the Top 6 tools you should know for writing AWESOME Select statements. If you want to learn more about other extremely common and useful query tools, check out that tutorial.

Thank you very much for reading!

Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!

If you have any questions, please don’t hesitate to leave a comment. Or better yet, send me an email!