Multiple table JOINs in MySQL

Introduction

Joining multiple tables helps you combine related information from different places into one view.

You want to see customer orders along with product details.

You need to list employees with their department and manager names.

You want to analyze sales data with customer and product info together.

You want to combine student grades with course and teacher details.

Syntax

MySQL

SELECT columns
FROM table1
JOIN table2 ON table1.column = table2.column
JOIN table3 ON table2.column = table3.column;

Use JOIN to connect tables based on matching columns.

Each JOIN adds another table to combine data from.

Examples

Join two tables: orders and customers to get order IDs with customer names.

MySQL

SELECT orders.id, customers.name
FROM orders
JOIN customers ON orders.customer_id = customers.id;

Join three tables to get order IDs, customer names, and product names.

MySQL

SELECT orders.id, customers.name, products.name
FROM orders
JOIN customers ON orders.customer_id = customers.id
JOIN products ON orders.product_id = products.id;

Sample Program

This example creates three tables and joins them to show order ID, customer name, and product name together.

MySQL

CREATE TABLE customers (id INT, name VARCHAR(20));
CREATE TABLE orders (id INT, customer_id INT, product_id INT);
CREATE TABLE products (id INT, name VARCHAR(20));

INSERT INTO customers VALUES (1, 'Alice'), (2, 'Bob');
INSERT INTO products VALUES (10, 'Pen'), (20, 'Notebook');
INSERT INTO orders VALUES (100, 1, 10), (101, 2, 20);

SELECT orders.id AS order_id, customers.name AS customer_name, products.name AS product_name
FROM orders
JOIN customers ON orders.customer_id = customers.id
JOIN products ON orders.product_id = products.id;

Important Notes

JOIN matches rows where the specified columns have the same value.

If a row in one table has no match in the other, it won't appear unless you use LEFT JOIN or RIGHT JOIN.

Order of JOINs matters when joining multiple tables; each JOIN uses the previous result.

Summary

Multiple table JOINs combine data from several tables into one result.

Use JOIN with ON to specify how tables connect.

This helps answer questions that need info from different tables.