Introduction
Relational databases spread data across multiple tables to avoid duplication - customers in one table, orders in another, products in a third.
A join is how SQL brings that data back together in a single query, matching rows from two or more tables based on a related column.
If you've ever needed to answer a question like "which customers placed orders last month?" or "which products have never been sold?", you needed a join.
This article breaks down what joins are, the main types available in SQL, when to reach for each one, and practical examples using PostgreSQL syntax.
We'll use two simple tables throughout:
CREATE TABLE customers (
customer_id SERIAL PRIMARY KEY,
customer_name VARCHAR(100)
);
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
customer_id INT REFERENCES customers(customer_id),
order_total NUMERIC(10, 2)
);
INSERT INTO customers (customer_name) VALUES
('Amina Otieno'),
('Brian Kamau'),
('Cynthia Wanjiru');
INSERT INTO orders (customer_id, order_total) VALUES
(1, 1500.00),
(1, 750.00),
(2, 300.00);
-- Note: Cynthia (customer_id 3) has no orders
-- Note: there's also an order with a customer_id that doesn't exist, added below
INSERT INTO orders (customer_id, order_total) VALUES (99, 200.00);
What Is a Join?
A join combines rows from two tables based on a related column between them. Usually a primary key in one table and a foreign key in the other. The database matches rows where the join condition is true and returns the combined result as a single set of rows.
The type of join you choose determines what happens to rows that don't have a match on the other side.
INNER JOIN
What it does: Returns only the rows where there's a match in both tables. Any row without a corresponding match on either side is excluded.
When to use it: When you only care about records that exist on both sides of the relationship. For example, customers who have actually placed at least one order.
SELECT c.customer_name, o.order_total
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;
Result: Amina and Brian appear (they have orders). Cynthia is excluded because she has no matching order, and the order with customer_id = 99 is excluded because there's no matching customer.
LEFT JOIN (LEFT OUTER JOIN)
What it does: Returns all rows from the left table, plus matching rows from the right table. Where there's no match, the right table's columns show as NULL.
When to use it: When you want to keep every record from your "main" table regardless of whether a related record exists. For example, listing all customers and showing their orders if they have any, including customers who haven't ordered yet.
SELECT c.customer_name, o.order_total
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;
Result: Amina and Brian appear with their order totals. Cynthia still appears, but order_total is NULL since she has no orders.
Common pattern — finding unmatched rows: Combine LEFT JOIN with a WHERE ... IS NULL check to find records with no match at all:
SELECT c.customer_name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;
This returns only Cynthia — the customer with zero orders.
RIGHT JOIN (RIGHT OUTER JOIN)
What it does: The mirror image of LEFT JOIN. Returns all rows from the right table, plus matching rows from the left table. Unmatched left-side columns show as NULL.
When to use it: Less common in practice - most people restructure the query as a LEFT JOIN by swapping table order instead, since it reads more intuitively. But it's useful when you want to keep the original table order in your FROM/JOIN clauses while still preserving all rows from the second table.
SELECT c.customer_name, o.order_total
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;
Result: All orders appear, including the one with customer_id = 99, which shows NULL for customer_name since no matching customer exists.
FULL JOIN (FULL OUTER JOIN)
What it does: Returns all rows from both tables. Where a match exists, columns are combined; where it doesn't, the missing side shows NULL.
When to use it: When you need a complete picture of both tables - matched and unmatched rows on either side. Useful for data quality checks, like finding orphaned records in either direction at once.
SELECT c.customer_name, o.order_total
FROM customers c
FULL JOIN orders o ON c.customer_id = o.customer_id;
Result: Amina and Brian's orders appear, Cynthia appears with NULL order total, and the orphaned order (customer_id = 99) appears with NULL customer name — all in one result set.
CROSS JOIN
What it does: Returns the Cartesian product of two tables - every row from the first table paired with every row from the second, with no matching condition at all.
When to use it: Rarely needed for typical reporting, but useful for generating combinations - like pairing every product with every size/color variant, or every day in a date range with every store location for a template table.
SELECT c.customer_name, o.order_id
FROM customers c
CROSS JOIN orders o;
Result: Every customer is paired with every order (3 customers × 4 orders = 12 rows), regardless of whether they're actually related.
SELF JOIN
What it does: Not a distinct join type, but a technique - joining a table to itself, usually to compare rows within the same table (e.g., employees and their managers, both stored in one employees table).
When to use it: Hierarchical or relational data within a single table.
CREATE TABLE employees (
employee_id SERIAL PRIMARY KEY,
name VARCHAR(100),
manager_id INT REFERENCES employees(employee_id)
);
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;
This matches each employee to their manager, who is just another row in the same table.
Choosing the Right Join: Quick Reference
| Join Type | Keeps unmatched rows from... | Typical use case |
|---|---|---|
INNER JOIN |
Neither side | Only records that exist in both tables |
LEFT JOIN |
Left table | All records from the main table, matches optional |
RIGHT JOIN |
Right table | Same as LEFT JOIN, tables reversed |
FULL JOIN |
Both sides | Complete picture, including orphaned records |
CROSS JOIN |
N/A (no condition) | Generating all possible combinations |
SELF JOIN |
Depends on join type used | Comparing rows within the same table |
Practical Example: Combining Concepts
A common real-world need: list every customer, their total spend, and flag those with no orders at all.
SELECT
c.customer_name,
COALESCE(SUM(o.order_total), 0) AS total_spent,
CASE WHEN COUNT(o.order_id) = 0 THEN 'No orders' ELSE 'Active' END AS status
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_name;
This uses a LEFT JOIN so every customer appears, COALESCE to turn NULL totals into 0, and a CASE statement to flag customers with no matching orders - a pattern that comes up constantly in reporting and dashboard work.
Key Takeaways
- Joins combine data from multiple tables based on a related column.
-
INNER JOINgives you only matched rows; outer joins (LEFT,RIGHT,FULL) preserve unmatched rows from one or both sides asNULL. -
LEFT JOINis the most commonly used outer join in practice. It's intuitive to reason about since you control which table's rows are always kept. -
CROSS JOINandSELF JOINsolve specific problems (combinations and same-table relationships) rather than everyday reporting needs. - Pairing
LEFT JOINwithWHERE ... IS NULLis a reliable pattern for finding unmatched or orphaned records, useful for data quality checks.
Once joins feel natural, most real-world SQL work becomes a matter of picking the right join for the shape of the answer you need.
Top comments (0)