Understanding JOINs in PostgreSQL

JOINs let you combine rows from two or more tables based on a related column. They are essential for working with normalized relational data where information is spread across multiple tables.

INNER JOIN

-- Only returns rows that have a match in both tables
SELECT
  o.id AS order_id,
  c.first_name,
  c.last_name,
  o.total,
  o.created_at
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;

An INNER JOIN returns only the rows where the join condition is met in both tables. If a customer has no orders, they are excluded. If an order references a deleted customer, it is also excluded.

LEFT JOIN

-- All customers, even those without orders
SELECT
  c.first_name,
  c.last_name,
  COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.first_name, c.last_name;

A LEFT JOIN returns all rows from the left table (customers) and matched rows from the right table (orders). Customers with no orders will show NULL for the order columns.

RIGHT JOIN and FULL JOIN

-- RIGHT JOIN: all orders, even without a valid customer
SELECT c.first_name, o.id AS order_id, o.total
FROM customers c
RIGHT JOIN orders o ON c.id = o.customer_id;

-- FULL JOIN: all rows from both tables
SELECT
  c.first_name,
  o.id AS order_id
FROM customers c
FULL JOIN orders o ON c.id = o.customer_id;

Joining Multiple Tables

SELECT
  c.first_name,
  o.id AS order_id,
  p.name AS product_name,
  oi.quantity,
  oi.unit_price
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.status = 'completed';

When to Use Each JOIN

• INNER JOIN -- when you only want rows that match in both tables
• LEFT JOIN -- when you need all rows from the primary table regardless of matches
• RIGHT JOIN -- rarely used; a LEFT JOIN with swapped table order is clearer
• FULL JOIN -- when you need unmatched rows from both sides, useful for data reconciliation