MySQL Joins

Joins are one of the most powerful features in SQL. They allow you to combine rows from two or more tables based on a related column between them. Understanding joins is essential for working with relational databases.

Why Use Joins?

In a normalized database, data is spread across multiple tables. Joins help you:

• Combine customer information with their orders
• Link products with their categories
• Connect employees with their departments
• Create comprehensive reports from related data

Sample Tables for Examples

+----+-----------+---------------+------------+
| id | name      | department_id | salary    |
+----+-----------+---------------+------------+
| 1  | John Doe  | 1             | 50000     |
| 2  | Jane Smith| 2             | 60000     |
| 3  | Bob Brown | 1             | 55000     |
| 4  | Alice Lee | NULL          | 48000     |
+----+-----------+---------------+------------+

+---------------+------------------+
| department_id | department_name  |
+---------------+------------------+
| 1             | Sales            |
| 2             | Marketing        |
| 3             | Engineering      |
+---------------+------------------+

Types of Joins in MySQL

Basic INNER JOIN Example

SELECT 
  e.name AS employee_name,
  d.department_name,
  e.salary
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

Result:

+---------------+---------------+--------+
| employee_name | department_name | salary |
+---------------+---------------+--------+
| John Doe      | Sales          | 50000  |
| Jane Smith    | Marketing      | 60000  |
| Bob Brown     | Sales          | 55000  |
+---------------+---------------+--------+
-- Note: Alice Lee is not included (no department_id)

Join Visualization

Join Multiple Tables

-- Three-table join example
SELECT 
  o.order_id,
  c.customer_name,
  p.product_name,
  oi.quantity,
  oi.unit_price,
  o.order_date
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date >= '2024-01-01'
ORDER BY o.order_date DESC;

Self Join

Join a table with itself (useful for hierarchical data):

-- Find employees and their managers
SELECT 
  e.employee_name AS employee,
  m.employee_name AS manager,
  e.department
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id
ORDER BY e.department, e.employee_name;

Join Performance Tips

• Always join on indexed columns
• Use the most selective join conditions first
• Avoid joining on computed columns
• Consider denormalization for frequently joined tables
• Use EXPLAIN to analyze join performance

Common Join Patterns

-- Get all products with their category and supplier info
SELECT 
  p.product_name,
  c.category_name,
  s.supplier_name,
  s.contact_email,
  p.price,
  p.stock_quantity
FROM products p
LEFT JOIN categories c ON p.category_id = c.category_id
LEFT JOIN suppliers s ON p.supplier_id = s.supplier_id
WHERE p.active = 1
ORDER BY c.category_name, p.product_name;

-- Find products never ordered
SELECT 
  p.product_id,
  p.product_name,
  p.price
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
WHERE oi.order_id IS NULL
  AND p.active = 1;

-- Find customers with no orders
SELECT 
  c.customer_id,
  c.customer_name,
  c.email
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL
  AND c.registration_date < DATE_SUB(CURDATE(), INTERVAL 30 DAY);

-- Department-wise salary report
SELECT 
  d.department_name,
  COUNT(e.employee_id) AS employee_count,
  AVG(e.salary) AS avg_salary,
  SUM(e.salary) AS total_salary,
  MIN(e.salary) AS min_salary,
  MAX(e.salary) AS max_salary
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
WHERE e.active = 1
  AND e.termination_date IS NULL
GROUP BY d.department_id, d.department_name
ORDER BY total_salary DESC;

Join Conditions Best Practices

1. Use table aliases for readability
2. Be explicit about join type (INNER/LEFT/RIGHT)
3. Put join conditions in ON clause, filter conditions in WHERE
4. Use foreign key columns for joining when possible
5. Test joins with LIMIT before running on full dataset