SQL inner join is a fundamental operation when it comes to retrieving data from multiple tables. When you are dealing with more than two tables, you can still use the inner join to combine rows from these tables based on a related column between them. Let's explore how you can perform an SQL inner join across three tables.
Before we dive into the inner join, ensure that your tables are properly structured and contain related data. This means that there are common columns between the tables where you can join them. For instance, if you have tables for 'Employees', 'Departments', and 'Salary', you can join them using the 'DepartmentID' column present in all three tables.
Understanding the Three Tables
To illustrate this, let's consider three tables: 'Customers', 'Orders', and 'OrderDetails'. The 'Customers' table holds information about customers, the 'Orders' table records orders placed by customers, and the 'OrderDetails' table specifies the products in each order. The 'CustomerID' column is common to the 'Customers' and 'Orders' tables, while the 'OrderID' column is shared between the 'Orders' and 'OrderDetails' tables.
Our goal is to retrieve a list of all customers along with the total number of orders they have placed and the total amount spent.
Joining 'Customers' and 'Orders' Table
The first step is to join the 'Customers' and 'Orders' tables using the 'CustomerID'. We'll use the inner join to combine rows from both tables where the 'CustomerID' matches. Here's the SQL query:
```sql SELECT Customers.CustomerID, Customers.Name, COUNT(Orders.OrderID) AS TotalOrders FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID GROUP BY Customers.CustomerID, Customers.Name; ```
This query returns a table with the 'CustomerID', 'Name', and 'TotalOrders' for each customer. The 'COUNT(Orders.OrderID)' function returns the total number of orders for each customer.
Joining the Result with the 'OrderDetails' Table
Now, we'll join the result of the previous query with the 'OrderDetails' table to get the total amount spent by each customer. We'll use the 'OrderID' as the common column to perform the inner join. Here's the updated SQL query:
```sql SELECT Customers.CustomerID, Customers.Name, COUNT(Orders.OrderID) AS TotalOrders, SUM(OrderDetails.Quantity * OrderDetails.UnitPrice) AS TotalAmountSpent FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID GROUP BY Customers.CustomerID, Customers.Name; ```
In this final query, we've added the 'TotalAmountSpent' column which calculates the total amount spent by each customer based on the 'Quantity' and 'UnitPrice' columns from the 'OrderDetails' table.
Executing the SQL Query
Once you have written your SQL query, you can execute it in your preferred SQL client or tool. The result will be a table with columns for 'CustomerID', 'Name', 'TotalOrders', and 'TotalAmountSpent'. This table summarizes the order activity for each customer.
The SQL inner join is a powerful tool for combining data from multiple tables. By mastering how to use it across three tables, you open the door to more complex data analysis and manipulation. Start practicing with different datasets to gain more experience and expand your SQL skills.
