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: