Left Join Example: Full Product Catalog Analysis

Scenario

In our e-commerce database, we want to create a report that lists all products, whether they have been ordered or not. For products that have been ordered, the report should include details of total sales and number of times ordered; for those not ordered, the report should still show the product with zero sales and zero orders.

SQL Query

Here’s the SQL query for this scenario using LEFT JOIN:

SELECT
    p.ProductID,
    p.ProductName,
    p.Price,
    COALESCE(SUM(od.Quantity), 0) AS TotalUnitsSold,
    COALESCE(SUM(od.Quantity * p.Price), 0.00) AS TotalSales,
    COUNT(DISTINCT o.OrderID) AS NumberOfOrders
FROM Products p
LEFT JOIN OrderDetails od ON p.ProductID = od.ProductID
LEFT JOIN Orders o ON od.OrderID = o.OrderID
GROUP BY p.ProductID
ORDER BY TotalSales DESC;

Explanation of the Code

1. Column Selection:
   • p.ProductID, p.ProductName, p.Price: Directly from the Products table.
   • COALESCE(SUM(od.Quantity), 0) AS TotalUnitsSold: Sum of all quantities ordered for each product. The COALESCE function ensures that if no orders exist, it shows 0 instead of NULL.
   • COALESCE(SUM(od.Quantity * p.Price), 0.00) AS TotalSales: Calculates the total revenue from each product. Using COALESCE to handle products that haven’t been sold.
   • COUNT(DISTINCT o.OrderID) AS NumberOfOrders: Counts the distinct number of orders for each product, again using COUNT with DISTINCT to ensure correct count and handling NULL with COALESCE.
2. LEFT JOINS:
   • LEFT JOIN OrderDetails od ON p.ProductID = od.ProductID: Ensures all products are listed, and if there are corresponding order details, they are included.
   • LEFT JOIN Orders o ON od.OrderID = o.OrderID: Connects order details to actual orders, necessary for counting the orders.
3. GROUP BY Clause: Necessary to aggregate data by ProductID since aggregate functions are used in the selection.
4. ORDER BY Clause: Orders products by TotalSales in descending order, focusing on higher revenue products at the top of the list.

Example Output

Assuming you have a set of product data, including some products that haven’t been sold yet, the output might look something like this: