What is an INNER JOIN?

-- Basic INNER JOIN combining customers and orders
SELECT 
  c.customer_name,
  o.order_id,
  o.order_date
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

-- Join three tables together
SELECT 
  c.customer_name,
  o.order_id,
  p.product_name,
  oi.quantity
FROM customers c
INNER JOIN orders o ON c.customer_id = o.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;

INNER JOIN Syntax

-- Using table aliases
SELECT 
  c.first_name,
  c.last_name,
  o.total_amount
FROM customers AS c
INNER JOIN orders AS o
  ON c.customer_id = o.customer_id;

-- Join with multiple conditions
SELECT 
  e.first_name,
  d.department_name
FROM employees e
INNER JOIN departments d
  ON e.department_id = d.department_id
  AND e.location = d.location;

INNER JOIN Behavior

-- INNER JOIN only returns matching rows
SELECT 
  c.customer_id,
  c.customer_name,
  COUNT(o.order_id) AS order_count
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name;

-- Filter on joined data
SELECT 
  p.product_name,
  SUM(oi.quantity) AS total_sold
FROM products p
INNER JOIN order_items oi ON p.product_id = oi.product_id
INNER JOIN orders o ON oi.order_id = o.order_id
WHERE o.order_date >= '2024-01-01'
GROUP BY p.product_name;

Common INNER JOIN Patterns

-- Self-join to find employees and their managers
SELECT 
  e.first_name AS employee,
  m.first_name AS manager
FROM employees e
INNER JOIN employees m ON e.manager_id = m.employee_id;

-- Join with calculated column
SELECT 
  c.customer_name,
  o.order_id,
  (oi.quantity * oi.unit_price) AS line_total
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id;

-- Join and aggregate
SELECT 
  d.department_name,
  COUNT(e.employee_id) AS employee_count,
  AVG(e.salary) AS avg_salary
FROM departments d
INNER JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name;