Joining Multiple Tables in a Single Query in MySQL

Course: MySQL Training

Topic: Joining Multiple Tables in a Single Query

Course Link: MySQL Training Course

Demo Link: Register for a Course Demo

1. Introduction to Joining Multiple Tables

In MySQL, joining multiple tables in a single query means retrieving related information from three or more tables using one SQL statement. Instead of running separate queries for each table, you can combine the required data using JOIN clauses and matching conditions.

In a relational database, information is often divided into multiple tables to reduce duplication and maintain consistency. For example, an e-commerce database may store customer information in a customers table, order information in an orders table, product information in a products table, and order-item details in an order_items table.

To display a customer's name, order date, product name, quantity, and price together, you can join these tables in a single query.

Learning Objectives

• Understand why MySQL queries sometimes need to combine multiple tables.
• Learn the syntax for joining three or more tables.
• Understand primary keys, foreign keys, and relationships between tables.
• Use INNER JOIN, LEFT JOIN, and other join types in multi-table queries.
• Apply table aliases to improve query readability.
• Use WHERE, ORDER BY, GROUP BY, HAVING, and aggregate functions with multiple joins.
• Identify common mistakes and understand how to troubleshoot incorrect results.
• Create practical reports using data from multiple related tables.

2. Why Do We Need to Join Multiple Tables?

Relational databases store different categories of information in separate tables. This structure helps reduce unnecessary duplication and makes data easier to manage.

For example, an online shopping database may have the following tables:

If these details are stored separately, one query can bring them together when a report requires information from all four tables.

Benefits of Joining Multiple Tables

• Combined information: Retrieve related information from several tables in one result.
• Reduced duplication: Avoid storing the same customer or product details repeatedly in transaction tables.
• Better reporting: Generate detailed sales, customer, employee, and inventory reports.
• Data consistency: Use table relationships to retrieve the correct related records.
• Flexible analysis: Filter, group, sort, and summarize data from different sources.
• Improved application queries: Retrieve the information needed for a page or report with a single SELECT statement.

3. Understanding Table Relationships Before Joining

Before joining multiple tables, it is important to understand how those tables are related. Most relational database joins use columns that connect one table to another.

Primary Key

A primary key uniquely identifies each row in a table. For example, customer_id can identify each customer in the customers table.

Foreign Key

A foreign key is a column or group of columns that references a key in another table. For example, orders.customer_id can reference customers.customer_id.

Common Relationships

Understanding these relationships helps you choose the correct join conditions and predict how many rows a query may return.

4. Basic Syntax for Joining Multiple Tables

The general syntax for joining three tables is:

SELECT
    table1.column_name,
    table2.column_name,
    table3.column_name
FROM table1
INNER JOIN table2
    ON table1.common_column = table2.common_column
INNER JOIN table3
    ON table2.common_column = table3.common_column;

You can add more JOIN clauses when additional tables are required.

Explanation of the Syntax

5. Creating Sample Tables for Multi-Table Joins

In this example, we will create four tables: customers, orders, products, and order_items. Together, these tables represent a simple online shopping database.

Step 1: Create the Customers Table

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    email VARCHAR(150)
);

Step 2: Insert Customer Records

INSERT INTO customers (customer_id, customer_name, email)
VALUES
(1, 'Amit Sharma', '[email protected]'),
(2, 'Priya Verma', '[email protected]'),
(3, 'Rahul Singh', '[email protected]'),
(4, 'Neha Patel', '[email protected]');

Step 3: Create the Orders Table

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    order_status VARCHAR(30) NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Step 4: Insert Order Records

INSERT INTO orders (order_id, customer_id, order_date, order_status)
VALUES
(101, 1, '2026-01-10', 'Completed'),
(102, 2, '2026-01-12', 'Completed'),
(103, 1, '2026-01-15', 'Pending'),
(104, 3, '2026-01-18', 'Completed');

Step 5: Create the Products Table

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL
);

Step 6: Insert Product Records

INSERT INTO products (product_id, product_name, unit_price)
VALUES
(201, 'Laptop', 55000.00),
(202, 'Mouse', 800.00),
(203, 'Keyboard', 1500.00),
(204, 'Monitor', 12000.00);

Step 7: Create the Order Items Table

CREATE TABLE order_items (
    order_item_id INT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

Step 8: Insert Order Item Records

INSERT INTO order_items
(order_item_id, order_id, product_id, quantity, unit_price)
VALUES
(1, 101, 201, 1, 55000.00),
(2, 101, 202, 2, 800.00),
(3, 102, 203, 1, 1500.00),
(4, 103, 204, 1, 12000.00),
(5, 104, 202, 3, 800.00);

The sample data provides several relationships: customers place orders, orders contain items, and each order item references a product.

6. Joining Three Tables in a Single Query

Suppose you want to display order IDs, customer names, and order dates. The information is stored in two tables: customers and orders.

SELECT
    o.order_id,
    c.customer_name,
    o.order_date
FROM orders AS o
INNER JOIN customers AS c
    ON o.customer_id = c.customer_id
ORDER BY o.order_id;

This query joins two tables. To include information from a third table, add another JOIN clause and its matching condition.

Example: Display Customer, Order, and Order Item Information

SELECT
    c.customer_name,
    o.order_id,
    o.order_date,
    oi.product_id,
    oi.quantity
FROM customers AS c
INNER JOIN orders AS o
    ON c.customer_id = o.customer_id
INNER JOIN order_items AS oi
    ON o.order_id = oi.order_id
ORDER BY o.order_id, oi.order_item_id;

How the Query Works

1. MySQL starts with the customers table.
2. The first INNER JOIN matches each order with its customer using customer_id.
3. The second INNER JOIN matches each order with its order items using order_id.
4. The SELECT clause displays the requested customer, order, and item information.
5. The ORDER BY clause sorts the output by order and item identifiers.

Because INNER JOIN requires a match at each step, the result includes only customer-order-item combinations that satisfy both join conditions.

7. Joining Four Tables in a Single Query

Now retrieve customer names, order dates, product names, quantities, and item prices from all four sample tables.

SELECT
    c.customer_name,
    o.order_id,
    o.order_date,
    p.product_name,
    oi.quantity,
    oi.unit_price
FROM customers AS c
INNER JOIN orders AS o
    ON c.customer_id = o.customer_id
INNER JOIN order_items AS oi
    ON o.order_id = oi.order_id
INNER JOIN products AS p
    ON oi.product_id = p.product_id
ORDER BY o.order_id, oi.order_item_id;

Expected Output

The same query retrieves information from four tables because the join conditions connect the customer to the order, the order to the order items, and each order item to its product.

8. Understanding Table Aliases

A table alias is a temporary name used for a table inside a query. Aliases help make multi-table queries shorter and easier to understand.

For example, instead of repeatedly writing customers.customer_name, you can use c.customer_name after assigning the alias c to the customers table.

SELECT
    c.customer_name,
    o.order_id,
    p.product_name
FROM customers AS c
INNER JOIN orders AS o
    ON c.customer_id = o.customer_id
INNER JOIN order_items AS oi
    ON o.order_id = oi.order_id
INNER JOIN products AS p
    ON oi.product_id = p.product_id;

Advantages of Table Aliases

• Reduce the length of SQL queries.
• Make relationships between tables easier to identify.
• Help distinguish columns with the same name in different tables.
• Improve readability when joining several tables.
• Make complex reporting queries easier to maintain.

Once you assign an alias, use it consistently when referring to that table's columns within the query.

9. Understanding Join Conditions with Multiple Tables

Every join condition should express the relationship between the tables being joined. Incorrect join conditions can produce inaccurate results or unexpectedly large result sets.

Correct Join Conditions

FROM customers AS c
INNER JOIN orders AS o
    ON c.customer_id = o.customer_id
INNER JOIN order_items AS oi
    ON o.order_id = oi.order_id
INNER JOIN products AS p
    ON oi.product_id = p.product_id

The relationships are:

• customers.customer_id = orders.customer_id
• orders.order_id = order_items.order_id
• order_items.product_id = products.product_id

What Happens If a Join Condition Is Incorrect?

If a condition connects unrelated columns, the result may contain invalid combinations. If a join condition is omitted from a join that should match related records, a Cartesian product may occur, increasing the result size dramatically.

Best practice: Identify the primary key and foreign key relationships before writing the query. Verify each ON condition independently when debugging a complex query.

10. Using INNER JOIN with Multiple Tables

INNER JOIN returns only rows that satisfy the join conditions. When several INNER JOIN clauses are used, a row must match the conditions at each join step to appear in the final result.

Example: Completed Customer Order Details

SELECT
    c.customer_name,
    o.order_id,
    p.product_name,
    oi.quantity
FROM customers AS c
INNER JOIN orders AS o
    ON c.customer_id = o.customer_id
INNER JOIN order_items AS oi
    ON o.order_id = oi.order_id
INNER JOIN products AS p
    ON oi.product_id = p.product_id
WHERE o.order_status = 'Completed'
ORDER BY o.order_date DESC;

This query retrieves item details only for orders marked Completed. The WHERE condition filters the joined records after the table relationships have been applied logically.

When Should You Use INNER JOIN?

• When you need only records that have matching related records.
• When creating reports for completed transactions with valid detail records.
• When retrieving product details for order items.
• When all joined relationships are required for a row to be meaningful in the result.

11. Using LEFT JOIN with Multiple Tables

LEFT JOIN is useful when you want to retain every record from the left-side table or joined result, even when related data is missing in a later table.

Example: Display All Customers and Their Orders

SELECT
    c.customer_id,
    c.customer_name,
    o.order_id,
    o.order_date
FROM customers AS c
LEFT JOIN orders AS o
    ON c.customer_id = o.customer_id
ORDER BY c.customer_id, o.order_date;

This query includes customers who have not placed an order. For such customers, the order columns contain NULL.

Example: Keep All Customers While Retrieving Order Items and Products

SELECT
    c.customer_name,
    o.order_id,
    oi.quantity,
    p.product_name
FROM customers AS c
LEFT JOIN orders AS o
    ON c.customer_id = o.customer_id
LEFT JOIN order_items AS oi
    ON o.order_id = oi.order_id
LEFT JOIN products AS p
    ON oi.product_id = p.product_id
ORDER BY c.customer_id, o.order_id;

This query preserves customers even if they have no orders. Where related records are absent, columns from the corresponding tables can contain NULL.

Important Point About LEFT JOIN

When you use LEFT JOIN across several tables, consider which records must be preserved. Adding a condition on a right-side table in the WHERE clause can remove rows containing NULL and may change the practical result into one that behaves like an INNER JOIN for that condition.

For example, if you want to keep every customer but match only completed orders, place the order-status condition in the ON clause:

SELECT
    c.customer_name,
    o.order_id,
    o.order_status
FROM customers AS c
LEFT JOIN orders AS o
    ON c.customer_id = o.customer_id
    AND o.order_status = 'Completed';

Customers without completed orders remain in the result, with NULL in the order columns.

12. Joining Tables with WHERE Clause

The WHERE clause filters rows returned by a multi-table query. You can use it to select particular customers, products, dates, or order statuses.

Example: Find Orders for a Particular Customer

SELECT
    c.customer_name,
    o.order_id,
    o.order_date,
    p.product_name
FROM customers AS c
INNER JOIN orders AS o
    ON c.customer_id = o.customer_id
INNER JOIN order_items AS oi
    ON o.order_id = oi.order_id
INNER JOIN products AS p
    ON oi.product_id = p.product_id
WHERE c.customer_name = 'Amit Sharma';

Example: Find Orders After a Particular Date

SELECT
    c.customer_name,
    o.order_id,
    o.order_date,
    p.product_name
FROM customers AS c
INNER JOIN orders AS o
    ON c.customer_id = o.customer_id
INNER JOIN order_items AS oi
    ON o.order_id = oi.order_id
INNER JOIN products AS p
    ON oi.product_id = p.product_id
WHERE o.order_date >= '2026-01-15'
ORDER BY o.order_date;

Example: Find Products Above a Certain Price

SELECT
    c.customer_name,
    o.order_id,
    p.product_name,
    oi.unit_price
FROM customers AS c
INNER JOIN orders AS o
    ON c.customer_id = o.customer_id
INNER JOIN order_items AS oi
    ON o.order_id = oi.order_id
INNER JOIN products AS p
    ON oi.product_id = p.product_id
WHERE oi.unit_price > 1000
ORDER BY oi.unit_price DESC;

In these examples, WHERE filters the joined rows based on customer name, order date, or item price.

13. Joining Multiple Tables with ORDER BY

The ORDER BY clause sorts the final result using one or more columns. In multi-table queries, you can sort by columns from any table included in the query.

Example: Sort Orders by Date

SELECT
    c.customer_name,
    o.order_id,
    o.order_date,
    p.product_name
FROM customers AS c
INNER JOIN orders AS o
    ON c.customer_id = o.customer_id
INNER JOIN order_items AS oi
    ON o.order_id = oi.order_id
INNER JOIN products AS p
    ON oi.product_id = p.product_id
ORDER BY o.order_date DESC, o.order_id DESC;

DESC sorts values in descending order, so the most recent order dates appear first. ASC sorts values in ascending order.

Example: Sort by Customer and Product

SELECT
    c.customer_name,
    p.product_name,
    oi.quantity
FROM customers AS c
INNER JOIN orders AS o
    ON c.customer_id = o.customer_id
INNER JOIN order_items AS oi
    ON o.order_id = oi.order_id
INNER JOIN products AS p
    ON oi.product_id = p.product_id
ORDER BY c.customer_name ASC, p.product_name ASC;

14. Joining Multiple Tables with GROUP BY

GROUP BY groups rows that share the same values in selected columns. It is commonly used with aggregate functions such as COUNT(), SUM(), AVG(), MIN(), and MAX().

Example: Calculate Total Spending for Each Customer

SELECT
    c.customer_id,
    c.customer_name,
    SUM(oi.quantity * oi.unit_price) AS total_spending
FROM customers AS c
INNER JOIN orders AS o
    ON c.customer_id = o.customer_id
INNER JOIN order_items AS oi
    ON o.order_id = oi.order_id
GROUP BY c.customer_id, c.customer_name
ORDER BY total_spending DESC;

The expression oi.quantity * oi.unit_price calculates the value of each order-item row. SUM() then adds those values for each customer.

Example: Count Products Purchased by Each Customer

SELECT
    c.customer_name,
    COUNT(oi.order_item_id) AS total_order_items
FROM customers AS c
INNER JOIN orders AS o
    ON c.customer_id = o.customer_id
INNER JOIN order_items AS oi
    ON o.order_id = oi.order_id
GROUP BY c.customer_id, c.customer_name
ORDER BY total_order_items DESC;

This query counts order-item records for customers who have matching orders and order items. It counts item rows, not necessarily distinct products or total units purchased.

Including Customers with No Orders

If you need every customer, including those without orders, use LEFT JOIN and count a non-NULL identifier from the right-side table.

SELECT
    c.customer_id,
    c.customer_name,
    COUNT(o.order_id) AS total_orders
FROM customers AS c
LEFT JOIN orders AS o
    ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name
ORDER BY c.customer_id;

COUNT(o.order_id) returns zero for customers without matching orders because COUNT(expression) ignores NULL values.

15. Using HAVING with Multiple Table Joins

The HAVING clause filters groups after GROUP BY has been applied. It is commonly used when you need to filter aggregate values such as total sales or number of orders.

Example: Customers with More Than One Order

SELECT
    c.customer_id,
    c.customer_name,
    COUNT(o.order_id) AS total_orders
FROM customers AS c
INNER JOIN orders AS o
    ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name
HAVING COUNT(o.order_id) > 1;

This query returns customers who have more than one matching order.

Example: Products with Total Sales Above a Threshold

SELECT
    p.product_id,
    p.product_name,
    SUM(oi.quantity * oi.unit_price) AS total_sales
FROM products AS p
INNER JOIN order_items AS oi
    ON p.product_id = oi.product_id
GROUP BY p.product_id, p.product_name
HAVING SUM(oi.quantity * oi.unit_price) > 5000
ORDER BY total_sales DESC;

WHERE filters individual rows before grouping, while HAVING filters the groups created by GROUP BY.

16. Joining Multiple Tables with Aggregate Functions

Aggregate functions help calculate totals, counts, averages, and other summary information from multiple related tables.

Example: Calculate Total Sales by Product

SELECT
    p.product_id,
    p.product_name,
    SUM(oi.quantity) AS total_units_sold,
    SUM(oi.quantity * oi.unit_price) AS total_sales
FROM products AS p
INNER JOIN order_items AS oi
    ON p.product_id = oi.product_id
GROUP BY p.product_id, p.product_name
ORDER BY total_sales DESC;

This query calculates the total quantity sold and the total sales amount for each product represented in order_items.

Example: Calculate the Average Order Item Price by Customer

SELECT
    c.customer_id,
    c.customer_name,
    AVG(oi.unit_price) AS average_item_price
FROM customers AS c
INNER JOIN orders AS o
    ON c.customer_id = o.customer_id
INNER JOIN order_items AS oi
    ON o.order_id = oi.order_id
GROUP BY c.customer_id, c.customer_name;

This average is calculated across matching order-item rows. It is not a weighted average based on quantity, nor is it the average total value of an order.

17. Using Different Join Types in One Query

A query can combine different join types when the requirements call for both matching records and the preservation of unmatched records. It is important to understand which rows should remain in the final result.

Example: Show All Customers and Matching Completed Orders

SELECT
    c.customer_name,
    o.order_id,
    o.order_status,
    oi.quantity,
    p.product_name
FROM customers AS c
LEFT JOIN orders AS o
    ON c.customer_id = o.customer_id
    AND o.order_status = 'Completed'
LEFT JOIN order_items AS oi
    ON o.order_id = oi.order_id
LEFT JOIN products AS p
    ON oi.product_id = p.product_id
ORDER BY c.customer_id, o.order_id;

This query preserves every customer and includes matching completed orders and their related item details when available. Customers without a completed order remain in the output with NULL values for the order-related columns.

Choosing the Appropriate Join Type

• INNER JOIN: Use when matching rows are required.
• LEFT JOIN: Use when every row from the left-side table or joined result must remain.
• RIGHT JOIN: Use when every row from the right table must remain, although rewriting the query with LEFT JOIN is often easier to read.
• CROSS JOIN: Use when every possible combination of rows is required.

18. Common Mistakes When Joining Multiple Tables

Mistake 1: Missing a Join Condition

If a relationship is intended but the matching condition is omitted, a query may produce a Cartesian product or an unexpectedly large result.

Solution: Check every JOIN clause and ensure that its ON condition connects the intended tables.

Mistake 2: Joining the Wrong Columns

Matching unrelated columns may return incorrect information, even when the query executes successfully.

Solution: Identify primary keys, foreign keys, and the actual relationship between the tables before writing the query.

Mistake 3: Ambiguous Column Names

Multiple tables may contain columns with the same name, such as customer_id, id, or created_at.

Solution: Qualify columns using table aliases, such as c.customer_id and o.customer_id.

Mistake 4: Unexpected Duplicate Rows

A one-to-many relationship can produce multiple rows for a single customer or order. For example, one order containing three order items appears as three result rows when item details are selected.

Solution: Check the relationship cardinality and decide whether you need detail rows, distinct records, or aggregated results.

Mistake 5: Filtering the Wrong Side of a LEFT JOIN

A WHERE condition on a right-side column may remove rows whose right-side values are NULL.

Solution: If unmatched left-side records must remain, consider placing the matching restriction in the ON clause instead.

Mistake 6: Incorrect Aggregate Calculations

Joining multiple one-to-many relationships can multiply rows and cause some aggregate totals to be counted more than once.

Solution: Inspect the row-level result first. If necessary, aggregate each detail table to the required level before joining the summaries.

Mistake 7: Using SELECT * in Complex Queries

SELECT * returns columns from every joined table and can create a wide result with repeated or confusing column names.

Solution: List only the required columns and use clear aliases for calculated fields.

19. Understanding Duplicate Rows and Aggregate Inflation

One of the most important concepts in multi-table joins is that a join can multiply records. This is expected when tables have one-to-many relationships, but it can cause incorrect totals if not understood.

Example: One Order with Multiple Items

If order 101 contains two order-item records, joining orders to order_items produces two rows for order 101. The order information repeats for each item.

SELECT
    o.order_id,
    oi.order_item_id,
    oi.quantity,
    oi.unit_price
FROM orders AS o
INNER JOIN order_items AS oi
    ON o.order_id = oi.order_id
WHERE o.order_id = 101;

This query correctly returns two rows for order 101 because the order contains two items.

Why Joining Multiple Detail Tables Can Inflate Totals

Suppose you join order_items with another table containing multiple shipments per order. If an order has two items and three shipment records, joining both detail tables at the order level can produce six combinations for that order. Summing the item totals from that combined result may count the same items multiple times.

Safer Approach: Aggregate Before Joining

SELECT
    o.order_id,
    o.order_date,
    item_totals.order_total
FROM orders AS o
LEFT JOIN (
    SELECT
        order_id,
        SUM(quantity * unit_price) AS order_total
    FROM order_items
    GROUP BY order_id
) AS item_totals
    ON o.order_id = item_totals.order_id;

The derived table calculates one total per order before the result is joined to orders. This helps avoid repeating item amounts when other one-to-many tables are also involved.

20. Improving Performance of Multi-Table Queries

Joining several tables can become expensive when tables contain large amounts of data. Good query design helps MySQL retrieve and combine information efficiently.

• Use appropriate indexes: Index columns commonly used in join conditions and selective filters where beneficial.
• Use compatible column types: Matching columns should have compatible data types and definitions.
• Select required columns: Avoid returning unnecessary fields from every table.
• Filter carefully: Use appropriate WHERE conditions to reduce the rows that need to be processed.
• Review table relationships: Confirm that each join matches the intended relationship.
• Aggregate at the right level: Summarize detail records before joining when needed to avoid repeated calculations.
• Use clear aliases: Readable queries are easier to debug and maintain.
• Inspect the execution plan: Use EXPLAIN to understand how MySQL plans to execute the query.

Example: Inspect a Multi-Table Query

EXPLAIN
SELECT
    c.customer_name,
    o.order_id,
    p.product_name,
    oi.quantity
FROM customers AS c
INNER JOIN orders AS o
    ON c.customer_id = o.customer_id
INNER JOIN order_items AS oi
    ON o.order_id = oi.order_id
INNER JOIN products AS p
    ON oi.product_id = p.product_id
WHERE o.order_status = 'Completed';

EXPLAIN provides information about the execution plan, including table access methods, possible indexes, selected keys, and estimated row counts. The most effective optimization depends on the actual data and query workload.

21. Practical Project: Generate a Complete Sales Report

Project Objective: Create a sales report that displays customer information, order details, product names, quantities, and item totals using multiple joined tables.

Task 1: Display Detailed Order Information

SELECT
    c.customer_name,
    c.email,
    o.order_id,
    o.order_date,
    o.order_status,
    p.product_name,
    oi.quantity,
    oi.unit_price,
    oi.quantity * oi.unit_price AS item_total
FROM customers AS c
INNER JOIN orders AS o
    ON c.customer_id = o.customer_id
INNER JOIN order_items AS oi
    ON o.order_id = oi.order_id
INNER JOIN products AS p
    ON oi.product_id = p.product_id
ORDER BY o.order_date DESC, o.order_id;

Task 2: Calculate Total Sales for Each Customer

SELECT
    c.customer_id,
    c.customer_name,
    SUM(oi.quantity * oi.unit_price) AS total_spending
FROM customers AS c
INNER JOIN orders AS o
    ON c.customer_id = o.customer_id
INNER JOIN order_items AS oi
    ON o.order_id = oi.order_id
GROUP BY c.customer_id, c.customer_name
ORDER BY total_spending DESC;

Task 3: Count Orders for Every Customer

SELECT
    c.customer_id,
    c.customer_name,
    COUNT(DISTINCT o.order_id) AS total_orders
FROM customers AS c
LEFT JOIN orders AS o
    ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name
ORDER BY c.customer_id;

COUNT(DISTINCT o.order_id) counts unique orders. This is especially useful when joining orders to order_items, because an order may have several item rows.

Task 4: Find Products That Have Been Ordered

SELECT
    p.product_id,
    p.product_name,
    SUM(oi.quantity) AS units_sold
FROM products AS p
INNER JOIN order_items AS oi
    ON p.product_id = oi.product_id
GROUP BY p.product_id, p.product_name
ORDER BY units_sold DESC;

Task 5: Include Products That Have Never Been Ordered

SELECT
    p.product_id,
    p.product_name,
    COALESCE(SUM(oi.quantity), 0) AS units_sold
FROM products AS p
LEFT JOIN order_items AS oi
    ON p.product_id = oi.product_id
GROUP BY p.product_id, p.product_name
ORDER BY units_sold DESC, p.product_name;

This query includes every product. Products without matching order-item records receive a units_sold value of 0.

Project Learning Outcomes

• Combine four related tables in a single SELECT statement.
• Retrieve detailed transactional information.
• Use INNER JOIN and LEFT JOIN according to reporting needs.
• Calculate item totals and customer spending.
• Count distinct orders accurately.
• Identify products without sales records.
• Understand how one-to-many relationships affect result rows and aggregate calculations.

22. Practice Exercises

Complete these exercises to strengthen your understanding of joining multiple tables.

1. Join customers and orders to display each customer's name and order date.
2. Join orders, order_items, and products to display product details for every order item.
3. Join customers, orders, order_items, and products to create a detailed sales report.
4. Find customers who have placed more than one order.
5. Calculate the total quantity sold for each product.
6. Display all customers, including customers who have never placed an order.
7. Display all products, including products that have never been ordered.
8. Use WHERE to display only completed orders.
9. Use GROUP BY and SUM() to calculate total spending per customer.
10. Use HAVING to display customers whose total spending exceeds a chosen amount.
11. Explain why an order may appear several times in a result containing order-item details.
12. Explain how joining two detail tables can inflate aggregate totals.
13. Use EXPLAIN to inspect a query that joins four tables.
14. Rewrite a long query using table aliases to make it easier to read.

23. Interview Questions and Answers

Q1. What does joining multiple tables mean in MySQL?

It means combining related data from three or more tables in one query using JOIN clauses and appropriate matching conditions.

Q2. Why are multiple tables joined in a single query?

To retrieve related information together without running separate queries for each table, such as customer details, order information, and product details.

Q3. What is the purpose of the ON clause?

The ON clause defines the condition used to match rows between joined tables.

Q4. What is a table alias?

A table alias is a temporary name assigned to a table within a query. It makes queries shorter and helps distinguish columns from different tables.

Q5. What happens when INNER JOIN is used across several tables?

A result row must satisfy the matching conditions at each INNER JOIN step to appear in the final result.

Q6. How is LEFT JOIN different from INNER JOIN?

LEFT JOIN preserves every row from the left-side table or joined result, whereas INNER JOIN returns only matching rows.

Q7. Can a query join more than three tables?

Yes. MySQL supports joining multiple tables in one query, subject to its documented limits and practical resource constraints.

Q8. Why can a multi-table query return duplicate-looking rows?

One-to-many relationships can cause a record from one table to appear multiple times when it matches several rows in another table. The rows may represent different details rather than accidental duplicates.

Q9. How can you count unique orders when order items are joined?

Use COUNT(DISTINCT o.order_id) when the goal is to count unique orders rather than the number of matching item rows.

Q10. What is the difference between WHERE and HAVING?

WHERE filters rows before grouping, while HAVING filters groups after GROUP BY and is commonly used with aggregate functions.

Q11. How can you prevent aggregate totals from being counted multiple times?

Understand the relationships and row counts, aggregate detail tables at the correct level before joining when appropriate, and verify the query's row-level output.

Q12. How do you troubleshoot a slow multi-table query?

Use EXPLAIN, review join conditions, inspect indexes, reduce unnecessary columns and rows, and check whether one-to-many relationships are creating more rows than expected.

24. Quick Revision Checklist

• Multiple-table joins combine related data from three or more tables.
• Primary keys uniquely identify rows.
• Foreign keys can connect related tables.
• Each JOIN should use an appropriate matching condition when relationships are intended.
• Table aliases make complex queries easier to read.
• INNER JOIN returns matching rows at each join step.
• LEFT JOIN preserves rows from the left-side result.
• WHERE filters rows, while HAVING filters groups.
• GROUP BY is used with aggregate functions to summarize data.
• One-to-many relationships can cause repeated values in the result.
• COUNT(DISTINCT ...) can count unique entities when detail rows multiply.
• Aggregate detail tables before joining when needed to prevent inflated totals.
• Use EXPLAIN to inspect query execution plans.
• Choose join types based on which records must appear in the final result.

25. Conclusion

Joining multiple tables in a single MySQL query is an essential skill for working with relational databases. It allows you to combine customer, order, product, employee, department, and other related information into one meaningful result.

To write accurate multi-table queries, understand the relationships between tables, choose suitable JOIN types, use correct ON conditions, and qualify column names with table aliases. When reporting totals, pay particular attention to one-to-many relationships because they can repeat rows and affect aggregate calculations.

Practice joining two, three, and four tables, then add filtering, sorting, grouping, and aggregate functions as needed. These skills will help you build reliable reports and solve real-world database problems.

To learn more about database querying, explore the MySQL Training Course at JustAcademy or register for a course demo.

26. Additional Learning Resource

For official syntax and examples, read the MySQL JOIN Clause Documentation.