MYSQL Tutorial

MySQL Multi-Table Joins

Joining more than two tables lets you combine data from multiple related tables in one query. You can chain JOINs by adding extra ON conditions.

Syntax Overview

SELECT …
FROM table1 AS t1
  JOIN table2 AS t2 ON t1.key2 = t2.key2
  JOIN table3 AS t3 ON t2.key3 = t3.key3
  /* add more JOINs as needed */
WHERE …;
  

Example: Three-Table INNER JOIN

Get each employee’s name, department, and office city:

SELECT 
  e.first_name, 
  e.last_name, 
  d.department_name, 
  l.city
FROM employees AS e
INNER JOIN departments AS d 
  ON e.department_id = d.department_id
INNER JOIN locations AS l 
  ON d.location_id = l.location_id;
  

Example: Mixing JOIN Types

List all customers, their orders (if any), and the sales rep’s name (if assigned):

SELECT 
  c.customer_name, 
  o.order_id, 
  sr.first_name AS sales_rep_first,
  sr.last_name  AS sales_rep_last
FROM customers AS c
LEFT JOIN orders AS o 
  ON c.customer_id = o.customer_id
LEFT JOIN sales_reps AS sr 
  ON o.sales_rep_id = sr.rep_id;
  

Tips for Multi-Table Joins

• Always use table aliases to keep your queries readable.
• Chain JOINs in logical order: start from the “main” table, then join related tables.
• Be mindful of JOIN type: INNER drops non-matching rows, LEFT/RIGHT preserves one side.
• Test each JOIN step with a simple SELECT * to verify correctness before adding more.
• Use EXPLAIN to inspect performance and ensure indexes support each ON condition.