Inner Join Example: Analyzing E-commerce Orders

Scenario

Imagine we need to create a report that shows all orders placed by customers, along with detailed information about the products in each order, including the product names and the quantities ordered. We will also calculate the total cost for each item in the order.

SQL Query

Here’s how you can write this SQL query using INNER JOIN:

SELECT
    c.FirstName,
    c.LastName,
    o.OrderID,
    o.OrderDate,
    p.ProductName,
    od.Quantity,
    p.Price,
    (od.Quantity * p.Price) AS ItemTotal
FROM Customers c
INNER JOIN Orders o ON c.CustomerID = o.CustomerID
INNER JOIN OrderDetails od ON o.OrderID = od.OrderID
INNER JOIN Products p ON od.ProductID = p.ProductID
ORDER BY o.OrderDate DESC, o.OrderID, p.ProductName;

Explanation of the Code

1. Column Selection:
   • c.FirstName, c.LastName: Customer’s first and last names from the Customers table.
   • o.OrderID, o.OrderDate: Order ID and the date the order was placed from the Orders table.
   • p.ProductName, p.Price: Product name and price per unit from the Products table.
   • od.Quantity: Quantity of each product ordered from the OrderDetails table.
   • (od.Quantity * p.Price) AS ItemTotal: Calculates the total cost for each product in the order.
2. INNER JOINS:
   • INNER JOIN Orders o ON c.CustomerID = o.CustomerID: Links the Customers table to the Orders table by CustomerID, ensuring we only get orders that have a corresponding customer.
   • INNER JOIN OrderDetails od ON o.OrderID = od.OrderID: Connects the Orders table to the OrderDetails table by OrderID, allowing us to retrieve details about what was ordered.
   • INNER JOIN Products p ON od.ProductID = p.ProductID: Links the OrderDetails table to the Products table by ProductID, enabling access to product details for each order item.
3. ORDER BY Clause:
   • Orders the results first by OrderDate in descending order to show the most recent orders first.
   • Then orders by OrderID and ProductName to group items in the same order together and sort products alphabetically within each order.

Example Output

Assuming you have populated the tables with relevant data, the output might look something like this: