SQL Left Join for Data Analysis

4 min read – Mar 13, 2023

When it comes to data analysis and reporting, one of the most important concepts to understand is SQL joins. SQL joins allow you to combine data from two or more tables into a single result set. In this article, we’ll focus specifically on the LEFT JOIN operation and how it can be used to create an analysis report using data from the Northwind database in SQL Server. The previous article was about the INNER JOIN

What is a LEFT JOIN?

A LEFT JOIN is a type of join in SQL that returns all the rows from the left table and the matching rows from the right table. If there are no matching rows in the right table, the result set will still include all the rows from the left table, with NULL values in the columns from the right table.

Let’s illustrate this with an example. Suppose we have two tables, Customers and Orders, with the following data:

Customers Table:

Orders Table:

If we want to retrieve all the customers and their orders (if any), we can use a LEFT JOIN between the Customers and Orders tables, with the Customers table as the left table and the Orders table as the right table. The syntax for a LEFT JOIN in SQL Server is as follows:

SELECT 
  c.CustomerID, 
  c.CustomerName, 
  o.OrderID, 
  o.OrderDate
FROM Customers c
  LEFT JOIN Orders o ON c.CustomerID = o.CustomerID;

The result set of this query will be as follows:

As you can see, the result set includes all the customers from the Customers table, and their corresponding orders (if any) from the Orders table. Note that the customer “Berglunds” has no orders, so the columns from the Orders table are NULL for that row.

Using LEFT JOIN to Create An Analysis Report

Now that we understand how a LEFT JOIN works, let’s use it to create an report using data from the Northwind database in SQL Server. The Northwin database is a sample database that contains sales data for a fictitious company that sells food products. We’ll focus on two tables from this database: Orders and Order Details.

Orders Table:

Order Details Table:

Suppose we want to create a report that shows the total sales revenue for each country, including countries that haven’t made any orders. We can achieve this by using a LEFT JOIN between the Orders and Order Details tables, with the Orders table as the left table and the Order Details table as the right table.

The SQL query for this report would be as follows:

SELECT 
  Orders.ShipCountry, 
  SUM(OrderDetails.UnitPrice * OrderDetails.Quantity) as TotalRevenue
FROM Orders
  LEFT JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID
GROUP BY Orders.ShipCountry
ORDER BY TotalRevenue DESC;

Let’s break down this query step by step:

1. We select the ShipCountry column from the Orders table and calculate the TotalRevenue by multiplying the UnitPrice and Quantity columns from the Order Details table and summing them up.
2. We use a LEFT JOIN to include all the rows from the Orders table, even if there are no matching rows in the Order Details table.
3. We join the two tables on the OrderID column, which is the common column between them.
4. We group the result set by the ShipCountry column, so that we get the total revenue for each country.
5. Finally, we order the result set in descending order by the TotalRevenue column.

The result set of this query will be as follows:

conclusion

LEFT JOIN is a powerful tool in your SQL data analyst toolkit that allows you to combine two or more tables together to create meaningful insights into your data. By using a LEFT JOIN, you can retain all the rows from one table while still joining with another table that may not have a matching row, allowing you to explore relationships between tables even when there are missing data points.

When using a LEFT JOIN, it’s important to specify the left table and the right table, and to ensure that the join condition is set up correctly so that you’re joining on the appropriate columns. Additionally, you’ll want to be mindful of your GROUP BY and ORDER BY clauses to ensure that your data is grouped and sorted appropriately.

Overall, LEFT JOINs are a crucial tool in any SQL developer’s toolkit, and can be used to create insightful reports and analyses that allow you to make data-driven decisions.