SQL LEFT JOIN

The SQL Server LEFT OUTER JOIN returns all the rows or records present in the left table and matches rows from the right table. The LEFT JOIN uses a common column in two tables to combine them.

  • If there is no matching record on the right table, those records will be filled with NULL values. It means even if there is no match, the LEFT JOIN simply returns all the records in the first table.
  • If any row present in the right table has no matching record in the left table, it won’t display in the result set (ignored).

The concept of SQL LEFT JOIN is helpful when we need all the records in the primary table, irrespective of the secondary table information. For instance, a list of customers regardless of their orders (both placed and not placed), products with sales and without, etc.

Both LEFT JOIN and LEFT OUTER JOIN are identical, so you can use either of them to produce the same result. Before proceeding to the practical example, let me show you the visual representation, or Venn diagram, of the SQL Server LEFT JOIN.

The image below displays all the records present in Table1 (orange fill) and matching records from Table2. All unmatched rows from the right table will fill with NULL Values.

SQL LEFT OUTER JOIN

TIP: While performing the LEFT OUTER JOIN, the order of joining the tables plays a vital role.

SQL LEFT JOIN Syntax

The syntax of the LEFT JOIN clause is as shown below.

SELECT Table1.Column(s), Table2.Column(s),
FROM Table1
LEFT OUTER JOIN Table2 
ON Table1.Common_Column = Table2.Common_Column

--OR We can Simply Write it as
SELECT Table1. Column(s), Table2. Column(s),
FROM Table1
LEFT JOIN Table2 
ON Table1.Common_Column = Table2.Common_Column

In the above LEFT JOIN syntax:

  • Table1 is the left side table.
  • Table2 is the right side table.
  • Common_column should be the same in both tables.

The LEFT JOIN can also be called a LEFT OUTER JOIN. So it is optional to use the Outer Keyword. To demonstrate the SQL Server LEFT JOIN with multiple examples that include aggregations, filters, and other features, we use the three tables shown below. Here, we create Products, Customers, and InternetSales tables based on the AdventureWorksDW database. To simplify the result, we restricted the records to a small number.

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100),
    Category VARCHAR(50),
    ListPrice DECIMAL(10, 2)
);

INSERT INTO Products (ProductID, ProductName, Category, ListPrice) VALUES
(1, 'Laptop', 'Electronics', 2000.00),
(2, 'Phone', 'Electronics', 800.00),
(3, 'Pants', 'Clothing', 150.00),
(4, 'Football', 'Sports', 20.00);  -- This product will have no sales

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(100),
    Country VARCHAR(50),
    State VARCHAR(50),
    City VARCHAR(50)
);

INSERT INTO Customers (CustomerID, Name, Country, State, City) VALUES
(1, 'John', 'USA', 'CA', 'San Francisco'),
(2, 'Ram', 'INDIA', 'UP', 'Lucknow');

-- InternetSales table
CREATE TABLE InternetSales (
    ProductID INT,
    CustomerID INT,
    OrderDate DATE,
    Quantity INT,
    Sales DECIMAL(10, 2),
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
INSERT INTO InternetSales (ProductID, CustomerID, OrderDate, Quantity, Sales) VALUES
(1, 1, '2025-01-01', 2, 4400.00), 
(2, 1, '2025-02-14', 1, 900.00),   
(3, 2, '2025-07-05', 4, 800.00),  
(1, 2, '2025-04-20', 1, 2100.00),  
(3, 2, '2025-05-20', 2, 350.00),  
(2, 2, '2025-02-14', 3, 2550.00);

SQL LEFT JOIN Example

The following series of examples helps you understand the LEFT OUTER JOIN with all possible options. One can use these examples to implement in real-time projects.

For instance, the following query utilises the SELECT * FROM statement in conjunction with the LEFT JOIN. The query below displays all products from the Products table, along with their corresponding sales in the InternetSales table.

SELECT * FROM Products p 
LEFT JOIN InternetSales s
ON p.ProductID = s.ProductID

TIP: Always use the index columns in the ON clause to establish the LEFT JOIN between tables.

Avoid OUTER Keyword

In SQL Server, there are two ways to implement the LEFT JOIN: either use the OUTER keyword or not; the query returns the same result.

Let me remove the OUTER keyword from the LEFT JOIN, which is optional, and it works well. As you can see, it returns rows from both tables in the result set.

SELECT * FROM Products p 
LEFT OUTER JOIN InternetSales s
ON p.ProductID = s.ProductID

As shown in the query result set, it returns seven records from both tables.

  • It uses the ProductID (common column) from both tables to establish a join between the Products and InternetSales tables.
  • The SQL Server LEFT JOIN returns all the records present in the Products table.
  • Next, as there is sales information for each product, it displays the matching records from the InternetSales table.
  • Although the LEFT JOIN displays 7 records, the sales information for the last row returns NULL values. It is because there are no sales (no one has purchased) Football (ProductID 4) in the InternetSales table, so there are no matching records.

NOTE: The LEFT JOIN returns all rows from the Products table, even if there is no matching record in the InternetSales table.

Choose Few Columns

If you look closely at the above result set, ProductID is repeated twice, and an unwanted CustomerID column, which is annoying to the user. By selecting individual column names, we can avoid unwanted ones in the SQL LEFT JOIN result set. So, please avoid SELECT * Statements.

To achieve this, please place the required columns after the SELECT Statement to avoid unwanted Server columns in JOINS. Here, we chose ProductName, Category, ListPrice, OrderDate, Quantity, and Sales columns from two tables.

SELECT ProductName, Category, ListPrice, OrderDate, Quantity, Sales
FROM Products LEFT JOIN InternetSales
ON Products.ProductID = InternetSales.ProductID

Importance of table ALIAS Names in SQL LEFT JOIN

The above query works as long as the column names in both tables are different, like above. What if we have identical Column names in both tables? It will throw an error. For instance, we use the same query as above, but we will add the ProductID column from the Products table as an additional column.

SELECT ProductID, ProductName, Category, ListPrice, OrderDate, Quantity, Sales
FROM Products LEFT JOIN InternetSales
ON Products.ProductID = InternetSales.ProductID

Message

Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'ProductID'.

As you can see from the message, the LEFT JOIN throws an error: Ambiguous column name ‘ProductID’. It is because the ProductID is present in both tables. And the server doesn’t understand which column you are requesting it to retrieve.

When you are working with SQL LEFT OUTER JOIN, ‘Ambiguous column’ is the most common error, and to resolve this, use ALIAS table names. The ALIAS table names not only help to avoid errors, it make the query more readable.

Let’s rewrite the above query with ALIAS table names. If you see the query below, we mention explicitly that we are looking for the ProductID that belongs to the Products table.

SELECT p.ProductID, p.ProductName, p.Category, s.OrderDate, s.Quantity, s.Sales
FROM Products p LEFT JOIN InternetSales s
ON p.ProductID = s.ProductID

Changing the Order of the Tables

The SQL Server LEFT OUTER JOIN is about prioritising the records from the left table. The order of the tables is very important. If you change the first table to InternetSales and the second to Products, it will return six records (instead of seven). As there is no matching sales information for ProductID 4 (right table), that record will be completely ignored for the result set.

SELECT s.ProductID, s.OrderDate, s.Quantity, s.Sales, p.ProductName, p.Category
FROM InternetSales s LEFT JOIN Products p
ON p.ProductID = s.ProductID
SQL LEFT JOIN Example

SQL LEFT JOIN with GROUP BY

In all the above examples, we displayed the products and their individual sales or the sales information and its corresponding products. In real-time scenarios, performing a LEFT JOIN is one task, but applying aggregations on the numeric columns is the actual task.

The following list of examples helps you understand the use of combining the LEFT JOIN, GROUP BY clause, and Aggregate functions.

Products and their Total Sales

The query below groups the result set of left joining Products and InternetSales tables using the product name and calculates the sum of sales. Here, we used the SUM() function, and you are free to test other aggregate functions.

SELECT p.ProductName, SUM(s.Sales) AS TotalSale
FROM Products p LEFT JOIN InternetSales s
ON p.ProductID = s.ProductID
GROUP BY p.ProductName

NOTE: Since there are no sales for the Football product, the query returns a NULL value for the total sales.

Customers and their Total Sales

We haven’t used the Customer table until now, so let’s find the total number of customers and their total sales, and the total number of orders.

SELECT c.Name, c.Country, c.State, c.City, 
	SUM(s.Sales) AS TotalSale, SUM(Quantity) AS Orders
FROM Customers c LEFT JOIN InternetSales s
ON c.CustomerID = s.CustomerID
GROUP BY c.Name, c.Country, c.State, c.City
Name	Country	State	City	TotalSale	Orders
John	USA	CA	San Francisco	5300.00	3
Ram	INDIA	UP	Lucknow	5800.00	10

If you want to use the Adventure Works DW database, use the query below. It will perform a LEFT JOIN on DimProduct and FactInternetSales tables and calculate the total order by product colour.

SELECT p.Color, SUM(f.OrderQuantity) AS TotalOrders
FROM DimProduct p LEFT JOIN FactInternetSales f 
ON p.ProductKey = f.ProductKey
GROUP BY p.Color
ORDER BY TotalOrders DESC;

SQL LEFT JOIN Multiple tables

The process of LEFT JOIN is not limited to two tables; you can use multiple tables and combine them using the common column. To demonstrate the concept of various tables, we will use the LEFT JOIN to combine three tables.

Here, the first LEFT JOIN combines the Products and InternetSales tables using the ProductID (common) column. Next, perform the LEFT JOIN with Customers using the common CustomerID column from the Customers and InternetSales tables. Next, the SELECT statement selects the required columns from three tables.

SELECT p.ProductID, p.ProductName, p.Category, 
	c.Name, c.Country, c.State, c.City,
	s.OrderDate, s.Quantity, s.Sales
FROM Products p 
LEFT JOIN InternetSales s ON p.ProductID = s.ProductID
LEFT JOIN Customers c ON c.CustomerID = s.CustomerID

Apart from LEFT joining three tables, you can use the GROUP BY and aggregate functions to calculate the total sales and order quantity by customer and category.

SELECT p.Category, c.Name, c.Country,
	SUM(s.Sales) AS TotalSale, SUM(Quantity) AS Orders
FROM Products p LEFT JOIN InternetSales s
ON p.ProductID = s.ProductID
LEFT JOIN Customers c
ON c.CustomerID = s.CustomerID
GROUP BY p.Category, c.Name, c.Country

TIP: Please be cautious when working with large tables as the performance of the LEFT JOIN becomes slower as the data grows.

SQL LEFT JOIN and WHERE Clause

The LEFT OUTER JOIN also permits us to use the WHERE clause to limit the number of rows returned by the query. To demonstrate the same, we use the same query that we mentioned in the ALIAS table names example and added a WHERE Clause to it. So, the following query displays the product sales information whose category is ‘Electronics’.

SELECT p.ProductID,p.ProductName, p.Category,
s.OrderDate, s.Quantity, s.Sales
FROM Products p LEFT JOIN InternetSales s
ON p.ProductID = s.ProductID
WHERE p.Category = 'Electronics'
SQL LEFT JOIN With GROUP BY and WHERE clause

NOTE: If there is no ‘Electronics’ category, the query returns an empty result set.

Similar to the above example, we can use the OrderDate (date and time) column and the BETWEEN operator to find the sales between two dates. The query below finds the product sales between January 2025 and 1st March 2025.

SELECT p.ProductID,p.ProductName, p.Category, 
s.OrderDate, s.Quantity, s.Sales
FROM Products p LEFT JOIN InternetSales s
ON p.ProductID = s.ProductID
WHERE s.OrderDate BETWEEN '2025-01-01' AND '2025-03-01'
ProductID	ProductName	Category	OrderDate	Quantity	Sales
1	Laptop	Electronics	2025-01-01	2	4400.00
2	Phone	Electronics	2025-02-14	1	900.00
2	Phone	Electronics	2025-02-14	3	2550.00

We can also use the WHERE clause while using LEFT JOIN to combine three tables. Here, the query below finds all the customers and their product sales information. The WHERE clause filters the customers without any sales.

SELECT p.Category, c.Name, c.Country,
	SUM(s.Sales) AS TotalSale, SUM(Quantity) AS Orders
FROM Products p 
LEFT JOIN InternetSales s ON p.ProductID = s.ProductID
LEFT JOIN Customers c ON c.CustomerID = s.CustomerID
WHERE c.Name IS NOT NULL
GROUP BY p.Category, c.Name, c.Country

NULL Values in LEFT JOIN

While working with the SQL Server LEFT OUTER JOIN, it is common to encounter NULL values within the result set. As we mentioned earlier, if there is no matching record on the right table, it returns NULL values. There are multiple ways to handle them, and you can use them to filter the records.

The following IS NULL query finds the products that have no sales to date. It is very important to identify these kinds of products so that you can make decisions, such as discounting, discontinuing them, etc.

SELECT p.ProductID,p.ProductName, p.Category, 
	s.OrderDate, s.Quantity, s.Sales
FROM Products p LEFT JOIN InternetSales s
ON p.ProductID = s.ProductID
WHERE s.ProductID IS NULL

If we use IS NOT NULL instead of using the IS NULL operator, the query will display all the products with at least one sale.

SELECT p.ProductID,p.ProductName, p.Category, 
	s.OrderDate, s.Quantity, s.Sales
FROM Products p LEFT JOIN InternetSales s
ON p.ProductID = s.ProductID
WHERE s.ProductID IS NOT NULL

If the intention is to handle the NULL values instead of filtering them, use the COALESCE function. The query below replaces the NULL values with 0.

SELECT p.ProductID,p.ProductName, p.Category, 
	SUM(COALESCE(s.Sales, 0)) AS Sales
FROM Products p LEFT JOIN InternetSales s
ON p.ProductID = s.ProductID
GROUP BY p.ProductID,p.ProductName, p.Category
SQL LEFT JOIN NULL Values

SQL LEFT JOIN and ORDER BY Clause

By default, the result set of the LEFT JOIN follows the data stored in the table. However, you can use the ORDER BY clause to alter the result set data. The query below returns country-wise sales information, sorted by the sum of sales amount in ascending order.

SELECT c.Country, c.State, c.City, SUM(s.Sales) AS TotalSale
FROM Customers c LEFT JOIN InternetSales s
ON c.CustomerID = s.CustomerID
GROUP BY c.Country, c.State, c.City
ORDER BY TotalSale ASC
Country	State	City	TotalSale
USA	CA	San Francisco	5300.00
INDIA	UP	Lucknow	5800.00

LEFT JOIN with Subquery

The SQL Server allows you to use the LEFT JOIN with the subquery concept to filter the grouped records. The query below returns all Products in the product table grouped by their names. Next, it finds the sum of the sales amount and order value. The subquery calculates the average sales of the InternetSales table. Next, the HAVING clause checks each product group’s sales value against the average and returns the products that return more than average sales.

SELECT p.ProductName, --p.Category,
    SUM(s.Sales) AS TotalSale, SUM(Quantity) AS Orders
FROM Products p LEFT JOIN InternetSales s
ON p.ProductID = s.ProductID
GROUP BY p.ProductName
HAVING SUM(s.Sales) > (SELECT AVG(Sales) FROM InternetSales)
ProductName	TotalSale	Orders
Laptop	6500.00	3
Phone	3450.00	4
Categories SQL