INNER, LEFT, RIGHT, and FULL JOIN

SQL JOINs combine rows from two or more tables based on a related column. INNER JOIN returns only rows where the join condition matches in both tables. LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the left table and matching rows from the right; unmatched right-side columns are NULL. RIGHT JOIN is the mirror image. FULL JOIN returns all rows from both tables, with NULLs where there is no match on either side. CROSS JOIN produces a Cartesian product — every combination of rows — and is occasionally useful for generating test data or combinations. PostgreSQL allows joining on any expression, not just equality, and supports the USING clause as a shorthand when the join columns have the same name.

Example

-- INNER JOIN — only users who have placed at least one order
SELECT u.name, u.email, COUNT(o.id) AS order_count
FROM users u
INNER JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name, u.email;

-- LEFT JOIN — all users, including those with no orders
SELECT u.name, COALESCE(COUNT(o.id), 0) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name;

-- Find users who have NEVER placed an order
SELECT u.name, u.email
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

-- FULL JOIN — all products and all categories, including orphans
SELECT p.name AS product, c.name AS category
FROM products p
FULL JOIN categories c ON c.id = p.category_id;

-- Multiple JOINs — order with customer and product details
SELECT o.id, u.name AS customer, p.name AS product, oi.qty, oi.unit_price
FROM orders o
JOIN users       u  ON u.id  = o.user_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products    p  ON p.id  = oi.product_id
WHERE o.status = 'shipped';

HTML

Full Editor

Related Resources

PostgreSQL Reference

Complete tag & property list

PostgreSQL How-To Guides

Step-by-step practical guides

PostgreSQL Exercises

Practice what you've learned