Inner Join

An INNER JOIN

returns only the rows where there is a match between both tables based on the join condition.

Task

Retrieve a list of customers, the orders they placed, and the payments made for each order. We’ll use INNER JOIN across these three tables: Customers, Orders, and Payments

Tables: Customers, Orders, and Payments

CustomerID	CustomerName
1	Alice
2	Bob
3	Charlie
4	David

OrderID	CustomerID	Amount
101	1	150
102	2	200
103	1	350
104	3	400

PaymentID	CustomerID	Amount
201	101	150
202	102	200
203	103	350

How Relationships Work in This Example

CustomerID is the primary key in the Customers table and acts as a foreign keyThis is the tooltip text! in the Orders table, connecting customers to their orders.
OrderID is the primary key in the Orders table and acts as a foreign key in the Payments table, linking orders to payments.

SQL Query for Advanced Inner Join:

         
    SELECT
        Customers.CustomerName,
        Orders.OrderID,
        Orders.Amount AS OrderAmount,
        Payments.Amount AS PaymentAmount
    FROM
        Customers
    INNER JOIN Orders
    ON Customers.CustomerID = Orders.CustomerID
    INNER JOIN Payments
    ON Orders.OrderID = Payments.OrderID;

Why This Query is Useful

Tracking payments associated with individual orders.
Generating detailed transaction histories for customers.
Identifying which orders have been paid and which remain unpaid.

Result Table

Query Output: Customer Orders with Payments

CustomerName	OrderID	OrderAmount	PaymentAmount
Alice	101	150	150
Alice	103	350	350
Bob	102	200	200

Why Are There Multiple Entries for Alice?

Alice placed two orders: OrderID 101 and 103.
Each of Alice's orders has a matching payment, so both appear in the result.
The INNER JOIN ensures that only orders with payments are shown.

Explanation

The Customers → Orders join matches CustomerID from the Customers table to CustomerID in the Orders table to retrieve all orders for each customer. Orders → Payments Join: Matches OrderID in the Orders table with the PaymentID in the Payments table to find payments made for each order. Inner Join Logic: Only rows with matching data across all three tables will be returned (i.e., customers with orders that have payments).

When to Use Inner Join

Relational data: When you want to retrieve only matching records from multiple tables. Filter irrelevant data: Ensures that only meaningful, linked data is returned. Common Scenarios: Finding customers with orders. Listing employees assigned to projects. Showing products with sales transactions.

Conclusion

There’s nothing wrong with having multiple entries for Alice in the original query because the INNER JOIN correctly reflects the one-to-many relationship between customers and orders. If you need detailed transaction information, the original query is perfect. If you need a summary, use the grouped query.