MySQL INNER JOIN

INNER JOIN is the most commonly used join type in SQL. It returns only the rows where there is a match in both tables based on the join condition. If there's no match, the row is not included in the result set.

Basic Syntax

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

Visual Representation

Sample Data

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

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

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
ORDER BY e.salary DESC;

Expected Result:

+---------------+---------------+--------+
| employee_name | department_name | salary |
+---------------+---------------+--------+
| Jane Smith    | Marketing      | 60000  |
| Bob Brown     | Sales          | 55000  |
| Mike Wilson   | Engineering    | 52000  |
| John Doe      | Sales          | 50000  |
+---------------+---------------+--------+
-- Note: Alice Lee is excluded (no department_id match)

Multiple INNER JOINs

-- Join three tables
SELECT 
  o.order_id,
  c.customer_name,
  p.product_name,
  oi.quantity,
  oi.unit_price,
  (oi.quantity * oi.unit_price) AS total_price
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;

INNER JOIN with WHERE Clause

-- Get sales employees earning more than 50000
SELECT 
  e.name,
  e.salary,
  d.department_name,
  d.location
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
WHERE d.department_name = 'Sales'
  AND e.salary > 50000
ORDER BY e.salary DESC;

INNER JOIN with GROUP BY

-- Department-wise salary summary
SELECT 
  d.department_name,
  COUNT(e.id) AS employee_count,
  AVG(e.salary) AS average_salary,
  SUM(e.salary) AS total_salary,
  MIN(e.salary) AS min_salary,
  MAX(e.salary) AS max_salary
FROM departments d
INNER JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name
ORDER BY total_salary DESC;

INNER JOIN with Aggregate Functions

-- Customer order summary
SELECT 
  c.customer_id,
  c.customer_name,
  COUNT(o.order_id) AS total_orders,
  SUM(o.total_amount) AS total_spent,
  AVG(o.total_amount) AS avg_order_value,
  MAX(o.order_date) AS last_order_date
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
GROUP BY c.customer_id, c.customer_name
HAVING total_orders >= 5
ORDER BY total_spent DESC;

INNER JOIN with Multiple Conditions

-- Complex join conditions
SELECT 
  e.employee_id,
  e.first_name,
  e.last_name,
  p.project_name,
  ep.role,
  ep.hours_worked
FROM employees e
INNER JOIN employee_projects ep ON e.employee_id = ep.employee_id
INNER JOIN projects p ON ep.project_id = p.project_id
WHERE ep.start_date <= CURDATE()
  AND (ep.end_date IS NULL OR ep.end_date >= CURDATE())
  AND p.status = 'Active'
  AND ep.role IN ('Lead', 'Developer')
ORDER BY e.last_name, e.first_name;

Self INNER JOIN

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

-- Find employees who work in the same department
SELECT 
  e1.name AS employee1,
  e2.name AS employee2,
  d.department_name
FROM employees e1
INNER JOIN employees e2 ON e1.department_id = e2.department_id
INNER JOIN departments d ON e1.department_id = d.department_id
WHERE e1.id < e2.id  -- Avoid duplicates and self-join
ORDER BY d.department_name, e1.name, e2.name;

INNER JOIN vs WHERE Join Syntax

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

SELECT 
  e.name,
  d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;

Performance Optimization

-- Add indexes for better performance
CREATE INDEX idx_employees_department ON employees(department_id);
CREATE INDEX idx_departments_id ON departments(department_id);

-- Use indexed columns in join conditions
SELECT 
  /*+ INDEX(e idx_employees_department) */
  e.employee_id,
  e.first_name,
  e.last_name,
  d.department_name,
  e.salary
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
WHERE e.active = 1
  AND d.location = 'Headquarters'
  AND e.salary BETWEEN 50000 AND 100000
ORDER BY e.last_name, e.first_name
LIMIT 100;

Common Use Cases

-- Get complete order details
SELECT 
  o.order_id,
  o.order_date,
  c.customer_name,
  c.email,
  c.phone,
  a.address_line1,
  a.city,
  a.state,
  a.zip_code,
  p.product_name,
  p.sku,
  oi.quantity,
  oi.unit_price,
  oi.discount,
  (oi.quantity * (oi.unit_price - oi.discount)) AS line_total,
  o.shipping_method,
  o.order_status,
  o.total_amount
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
INNER JOIN addresses a ON o.shipping_address_id = a.address_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.product_id
INNER JOIN order_status os ON o.status_id = os.status_id
WHERE o.order_date BETWEEN '2024-01-01' AND '2024-12-31'
  AND o.order_status IN ('Shipped', 'Delivered')
ORDER BY o.order_date DESC, o.order_id;

-- Student enrollment report
SELECT 
  s.student_id,
  CONCAT(s.first_name, ' ', s.last_name) AS student_name,
  s.date_of_birth,
  s.gender,
  c.course_code,
  c.course_name,
  c.credits,
  i.first_name AS instructor_first,
  i.last_name AS instructor_last,
  e.enrollment_date,
  e.grade,
  e.status,
  d.department_name,
  b.building_name,
  r.room_number,
  sch.schedule_day,
  sch.start_time,
  sch.end_time
FROM students s
INNER JOIN enrollments e ON s.student_id = e.student_id
INNER JOIN courses c ON e.course_id = c.course_id
INNER JOIN instructors i ON c.instructor_id = i.instructor_id
INNER JOIN departments d ON c.department_id = d.department_id
INNER JOIN class_schedules sch ON c.course_id = sch.course_id
INNER JOIN classrooms r ON sch.room_id = r.room_id
INNER JOIN buildings b ON r.building_id = b.building_id
WHERE e.academic_year = '2024-2025'
  AND e.semester = 'Fall'
  AND e.status = 'Active'
ORDER BY d.department_name, c.course_code, s.last_name, s.first_name;

Troubleshooting INNER JOIN Issues

Best Practices

1. Always use table aliases for readability
2. Use explicit INNER JOIN syntax
3. Add indexes on columns used in JOIN conditions
4. Test joins with LIMIT clause first
5. Use EXPLAIN to analyze join performance
6. Consider denormalization for frequently joined tables