SQL Joins

RIGHT JOIN for Right Outer Queries

RIGHT JOIN returns all rows from the right table and matching rows from the left table.

All Right Rows — includes every record from the joined table
Flexible Matching — NULLs fill where no left match exists Get complete data from the secondary table.

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

Orders Table	Customers Table	RIGHT JOIN Result
OrderID=101, CustID=1	ID=1, John	Order 101, John
OrderID=102, CustID=2	ID=2, Jane	Order 102, Jane
OrderID=103, CustID=5	No match	Order 103, NULL
OrderID=104, CustID=5	No match	Order 104, NULL

-- 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

RIGHT JOIN	Equivalent LEFT JOIN
A RIGHT JOIN B	B LEFT JOIN A
All rows from B	All rows from B
Matching rows from A	Matching rows from A
NULLs for unmatched A	NULLs for unmatched A

-- 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;

RIGHT JOIN for Right Outer Queries

RIGHT JOIN for Right Outer Queries

What is a RIGHT JOIN?

RIGHT JOIN Behavior

RIGHT JOIN vs LEFT JOIN Equivalence

Common RIGHT JOIN Patterns

Need Expert SQL Help?