MS SQL Server

SQL Server Left Join

One of the prominent features of Relational databases, such as SQL Server, is getting data from various tables and combining them to create new but related information. The way to accomplish this is by the use of SQL joins.

Joins are useful as they allow you to tell SQL Server how to use and organize the date from the selected tables. This helps to create a relationship between two or more tables.

This article will learn how to use the SQL LEFT JOIN in SQL Server.

Before proceeding, ensure you have SQL Server installed and running. We also recommend using SQL Server Management Studio to get similar output as the code samples.

The Basics

SQL Left join is a type of join that returns the rows from the left table regardless of a matching row on the right table. This differs from other joins, such as inner joins, that require at least one matching row in both the left and right tables.

Let’s take a simple example. Suppose we have table X and table Y. Table X has rows ( a b c d e) while table Y has rows: (d e f g h).

If we perform a left join on the tables, the resulting set will contain all the values from the left table, whether the rows match the table on the right or not.

We can express the syntax of an SQL left join as:

SELECT COLUMN(s)
FROM table1_name
LEFT JOIN table2_name
ON table1_name.col_name = table2_name.col_name;

In the syntax above, we start by selecting the target columns we wish to include in the result set next; we set the left join clause followed by the join condition specified after the ON clause.

Left Join By Example

Let us assume we have two tables containing customer information, such as CustomerID, FirstName, and LastName. The other table contains sales information such as SalesID, SalesPerson, CustomerID, ProductID, Quantity.

We can perform a left join to select all the customers and their sales. Consider the example SQL query shown below:

SELECT top 10 Customers.FirstName, Sales.SalesID
FROM Customers
LEFT JOIN Sales ON Customers.CustomerID = Sales.CustomerID

The example query above should return a resulting set as shown below:

The left join returns all the rows from the Customers table regardless of the unavailability of matching rows in the Sales table.

Example 2 – Left Join with Where clause

You can also specify a where condition to the query. This allows you to filter for specific results within a set table. For example, we can perform a left join on customers where the first name is John.

Consider the example shown below:

SELECT top 10 Customers.FirstName, Sales.SalesID
FROM Customers
LEFT JOIN Sales ON Customers.CustomerID = Sales.CustomerID
WHERE Customers.FirstName = 'John';

The query will only return the rows where the customer’s firstName is John. An example result set is as shown:

You notice that the LEFT JOIN query adds a NULL if there is no matching row.

Example 3 – Left Join with Order By clause

Adding an order by clause to a left join allows you to sort the values in the resulting set by a specific condition. For example, the query below sorts the result by the salesID in descending order.

SELECT top 10 Customers.FirstName, Sales.SalesID
FROM Customers
LEFT JOIN Sales ON Customers.CustomerID = Sales.CustomerID
ORDER BY Sales.SalesID DESC;

The query returns the values sorted by SalesID from highest to lowest. The resulting query is as shown below:

SQL Server: Left Join vs. Inner Join

If you are a new SQL beginner, choosing between a left join and an inner join can confuse you. To avoid confusion, only use left join when you need the resulting set to include all the rows from the left, even if there are no matching rows on the right table.

Inner join is useful when you need the resulting set to only include rows with matching pairs on the left and right table.

Closing

This article covers the basics of using SQL left join in SQL Server. You can create a resulting set of records using left joins even if there are no matching rows on the left and right tables.

Stay tuned for more SQL Server content!!

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list