What is a RIGHT JOIN?

-- RIGHT JOIN returns all orders, even without matching customers
SELECT 
  c.customer_name,
  o.order_id,
  o.order_date
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;

-- RIGHT JOIN with multiple tables
SELECT 
  c.customer_name,
  o.order_id,
  p.product_name,
  oi.quantity
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id
RIGHT JOIN order_items oi ON o.order_id = oi.order_id
RIGHT JOIN products p ON oi.product_id = p.product_id;

RIGHT JOIN Behavior

-- Find orders without matching customer records
SELECT 
  o.order_id,
  o.order_date,
  o.total_amount
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;

-- Count orders per customer (including customers with zero orders)
SELECT 
  c.customer_name,
  COUNT(o.order_id) AS order_count
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name;

RIGHT JOIN vs LEFT JOIN Equivalence

-- These two queries produce the same result:

-- RIGHT JOIN version
SELECT c.customer_name, o.order_id
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;

-- LEFT JOIN equivalent (table order swapped)
SELECT c.customer_name, o.order_id
FROM orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id;

-- Find all departments and their employees (including empty depts)
SELECT 
  d.department_name,
  e.employee_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;

-- Equivalent using LEFT JOIN
SELECT 
  d.department_name,
  e.employee_name
FROM departments d
LEFT JOIN employees e ON e.department_id = d.department_id;

Common RIGHT JOIN Patterns

-- RIGHT JOIN for complete reporting
SELECT 
  p.category,
  p.product_name,
  COALESCE(SUM(oi.quantity), 0) AS units_sold
FROM order_items oi
RIGHT JOIN products p ON oi.product_id = p.product_id
GROUP BY p.category, p.product_name;

-- RIGHT JOIN with aggregate functions
SELECT 
  d.department_name,
  COUNT(e.employee_id) AS employee_count,
  COALESCE(AVG(e.salary), 0) AS avg_salary
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name;

-- RIGHT JOIN for data completeness checks
SELECT 
  s.supplier_name,
  COUNT(p.product_id) AS product_count,
  COALESCE(AVG(p.unit_cost), 0) AS avg_cost
FROM products p
RIGHT JOIN suppliers s ON p.supplier_id = s.supplier_id
GROUP BY s.supplier_id, s.supplier_name;