How to Use JOINs in PostgreSQL

If there’s one SQL concept that separates “I can write a basic SELECT statement” from “I can actually build real applications with a database,” it’s joins. Almost nothing useful lives in a single table in a well-designed relational database — customers live in one table, their orders in another, order items in a third, products in a fourth. Joins are how you stitch that data back together into something meaningful. This is my complete overview of how joins work in PostgreSQL, covering every major join type and how to actually reason about which one you need.

What Is a JOIN, Conceptually?

A join combines rows from two or more tables based on a related column between them. Instead of running separate queries and manually matching data in your application code, you let PostgreSQL do the matching directly in SQL — which is faster, more reliable, and far less code to maintain.

Every join needs two things: the tables you’re combining, and a condition describing how rows from one table relate to rows in the other (usually a foreign key relationship).

Setting Up an Example Schema

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(id),
    amount NUMERIC(10,2)
);

INSERT INTO customers (name) VALUES
('Imran'), ('Sadia'), ('Noor');

INSERT INTO orders (customer_id, amount) VALUES
(1, 300.00),
(1, 150.00),
(2, 90.00);

Note Noor has never placed an order — I’ll use this to illustrate how each join type treats unmatched rows differently.

INNER JOIN — Only Matching Rows

SELECT c.name, o.amount
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;

Returns only customers who have at least one order. Noor is excluded entirely, since there’s no matching row in orders. INNER JOIN (or its shorthand, JOIN) is what you want when a row is only meaningful if both sides genuinely connect.

LEFT JOIN — Everything From the Left, Matched or Not

SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;

Returns every customer, including Noor, whose amount shows up as NULL since she has no orders. This is the join I reach for most often — “show me all of table A, plus related data from table B if it exists.”

RIGHT JOIN — Everything From the Right, Matched or Not

SELECT c.name, o.amount
FROM customers c
RIGHT JOIN orders o ON c.id = o.customer_id;

The mirror image of LEFT JOIN — returns every row from orders, with matching customer data where it exists. Since every order in our example has a valid customer, this looks identical to an INNER JOIN here, but if there were an order with an invalid or missing customer_id, that order would still appear with NULL for the customer name.

A RIGHT JOIN B is always equivalent to B LEFT JOIN A — it’s the same operation, just with the table order reversed.

FULL OUTER JOIN — Everything From Both Sides

SELECT c.name, o.amount
FROM customers c
FULL OUTER JOIN orders o ON c.id = o.customer_id;

Returns every row from both tables — matched rows show data from both sides, and unmatched rows on either side show NULL for whichever side is missing. This is the join to use when you specifically need to see gaps in both directions at once, like reconciling two datasets that are supposed to line up.

CROSS JOIN — Every Combination of Rows

SELECT c.name, p.size
FROM customers c
CROSS JOIN (VALUES ('Small'), ('Medium'), ('Large')) AS p(size)
);

A CROSS JOIN produces the Cartesian product of both tables — every row from the first table combined with every row from the second, with no matching condition at all. If customers has 3 rows and this size list has 3 rows, you get 9 rows total. This is genuinely useful for generating combinations — like every customer paired with every available size for a survey, or generating a date-by-category grid for reporting — but it’s dangerous when used accidentally, since it can produce enormous result sets on large tables.

SELF JOIN — Joining a Table to Itself

Sometimes the related data you need lives in the same table. A classic example is an employees table with a manager_id referencing another row in the same table:

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT REFERENCES employees(id)
);

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

This is called a self join — the same table is referenced twice under different aliases (e and m). I use LEFT JOIN here rather than INNER JOIN because top-level employees with no manager should still show up in the results, just with NULL for the manager column.

Joining Multiple Tables Together

Real applications rarely stop at two tables. Here’s a three-table join combining orders, order items, and products:

CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INT REFERENCES orders(id),
    product_id INT,
    quantity INT
);

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

SELECT c.name AS customer, p.name AS product, oi.quantity
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id;

Each join step narrows or extends the result based on its own condition. Mixing join types in a chain is also completely valid — for example, using LEFT JOIN for optional relationships while using INNER JOIN for mandatory ones within the same query.

Choosing the Right Join Type — A Practical Framework

Whenever I’m deciding which join to use, I ask myself one question: “Which rows absolutely must appear in my result, regardless of whether a match exists?”

• If both sides must match → INNER JOIN
• If I need everything from the left table, matched or not → LEFT JOIN
• If I need everything from the right table, matched or not → RIGHT JOIN (or just flip to LEFT JOIN)
• If I need everything from both tables, matched or not → FULL OUTER JOIN
• If I need every possible combination with no matching logic at all → CROSS JOIN
• If the related data lives in the same table → self join (using whichever join type fits the “must it always appear” question above)

The WHERE Clause Trap With Outer Joins

This applies to LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN alike: filtering on the “non-preserved” side’s column in WHERE can silently convert your outer join back into an inner join, because NULL values fail most WHERE conditions.

-- BUG: silently drops unmatched customers
SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.amount > 100;

Move such conditions into the ON clause if you need to preserve unmatched rows:

SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id AND o.amount > 100;

Performance Considerations Across Join Types

Every join benefits from indexes on the columns used in the join condition, especially foreign key columns, which PostgreSQL does not index automatically:

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

PostgreSQL’s planner chooses between a few join strategies internally — nested loop, hash join, and merge join — based on table sizes, available indexes, and up-to-date statistics. You don’t choose the strategy directly, but you influence it heavily through indexing and keeping table statistics fresh with ANALYZE. Always check with EXPLAIN ANALYZE on any join over a large table:

EXPLAIN ANALYZE
SELECT c.name, o.amount
FROM customers c
JOIN orders o ON o.customer_id = c.id;

Side-by-Side Comparison of Every Join Type

Sometimes it helps to see the behavior differences laid out directly against the same data, rather than one join type at a time. Using our customers/orders example, where Noor has no orders:

Keeping a mental table like this handy is genuinely useful when you’re deciding which join fits a new query — I still think through this table almost every time I’m not 100% sure which join type I need.

NATURAL JOIN — Why I Avoid It

PostgreSQL supports one more join keyword worth mentioning specifically so you know to avoid it: NATURAL JOIN. It automatically joins two tables based on all columns that share the same name, without you specifying any condition at all.

-- Avoid this
SELECT *
FROM orders
NATURAL JOIN customers;

This looks convenient, but it’s genuinely risky in real codebases. If someone adds a new column to either table later that happens to share a name with a column in the other table — even a completely unrelated column like created_at or notes — your NATURAL JOIN silently changes behavior, potentially breaking the query or producing wrong results with no error at all. I’ve never used NATURAL JOIN in production code, and I’d recommend avoiding it entirely in favor of explicit ON or USING clauses, which make the join condition obvious and stable regardless of future schema changes.

Common Use Cases

• INNER JOIN: order details with valid product references, comments tied to existing posts.
• LEFT JOIN: customer lists including those with zero orders, complete reports with optional related data.
• RIGHT JOIN: rare in hand-written code, common in generated/ORM SQL or when extending an existing query structure.
• FULL OUTER JOIN: reconciliation between two datasets that should align but might not.
• CROSS JOIN: generating combinations — dates × categories, sizes × products, for reporting grids.
• Self join: hierarchical data — employees and managers, category and parent category.

A Realistic Multi-Join Report

Let me pull several join concepts together into a query resembling something I’d actually build for a real application — an e-commerce order summary that needs required customer data, optional shipping notes, and a category breakdown:

SELECT
    c.name AS customer,
    o.id AS order_id,
    o.amount,
    sn.note AS shipping_note,
    p.category
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id
LEFT JOIN shipping_notes sn ON sn.order_id = o.id
INNER JOIN order_items oi ON oi.order_id = o.id
INNER JOIN products p ON p.id = oi.product_id
ORDER BY o.id;

Notice the mix of join types is deliberate, not arbitrary. customers to orders is an INNER JOIN because an order without a valid customer doesn’t make sense in this system. shipping_notes is a LEFT JOIN because most orders don’t have a special note attached, and I don’t want to exclude perfectly normal orders just because that optional field is empty. order_items and products are INNER JOIN again, because a line item without a valid product reference would indicate corrupted data I’d actually want surfaced as an error, not silently hidden by an outer join. Reading a query like this, you can reconstruct the business rules of the data model just from which join type was chosen at each step — which is exactly why being deliberate about join type, rather than defaulting to one type everywhere, makes queries genuinely more meaningful to future readers.

Troubleshooting Tips

Getting more rows than expected. Almost always a one-to-many or many-to-many relationship producing multiple output rows per matched entity. This is expected join behavior — aggregate with GROUP BY if you need one row per entity.

Missing rows I expected to see. Check whether you’re using INNER JOIN where an outer join was actually needed, or whether a WHERE clause is silently filtering out NULL rows from an outer join.

Query is very slow. Check for missing indexes on join columns first — this is the most common cause of slow joins on large tables. Run EXPLAIN ANALYZE to confirm whether PostgreSQL is doing a sequential scan where an index scan should be possible.

Accidental massive result set. Usually a CROSS JOIN used unintentionally, often from forgetting a join condition in older comma-separated join syntax. Always use explicit JOIN ... ON syntax to avoid this.

Best Practices I Follow

1. Always use explicit JOIN ... ON syntax, never the old comma-separated implicit style.
2. Index every foreign key column used in joins — PostgreSQL doesn’t do this automatically.
3. Default to LEFT JOIN for optional relationships and INNER JOIN for mandatory ones.
4. Move filtering conditions into ON when you need to preserve unmatched rows in outer joins.
5. Run EXPLAIN ANALYZE on any join involving large tables before deploying to production.
6. Alias your tables clearly (c for customers, o for orders) to keep multi-join queries readable.
7. Test joins incrementally when chaining more than two or three tables together.

Frequently Asked Questions

What’s the difference between JOIN and INNER JOIN? Nothing — JOIN is shorthand for INNER JOIN in PostgreSQL.

Which join type should I use by default? INNER JOIN when both sides must match for the row to make sense; LEFT JOIN when you want to preserve all rows from your main table regardless of matches. These two cover the vast majority of real-world queries.

Can I combine different join types in one query? Yes, absolutely. It’s common to use INNER JOIN for required relationships and LEFT JOIN for optional ones within the same multi-table query.

Does PostgreSQL support FULL OUTER JOIN? Yes, it’s fully supported and behaves according to the SQL standard — all rows from both tables, matched where possible.

How do I know which join strategy PostgreSQL is using internally? Run EXPLAIN ANALYZE on your query — it’ll show whether PostgreSQL chose a nested loop, hash join, or merge join, along with actual execution time and row counts.

Is there a limit to how many tables I can join in one query? No hard limit exists in PostgreSQL, but practically speaking, queries with a very large number of joined tables become harder for the planner to optimize efficiently and harder for humans to read and debug. If a query grows past six or seven joins, it’s often worth reconsidering whether some of that logic belongs in a view, a CTE, or a separate pre-aggregated table instead.

Should I ever use a subquery instead of a join? Sometimes — subqueries are often clearer for existence checks (EXISTS) or single aggregated comparisons, while joins are generally better when you need actual columns from both tables in your output. They’re not mutually exclusive; a well-written query often uses both together.

Wrapping Up

Joins are the foundation of everything meaningful you’ll do with a relational database. Once you internalize the core question — “which rows must survive, regardless of whether a match exists?” — choosing between INNER, LEFT, RIGHT, and FULL OUTER becomes almost automatic. Practice building multi-table queries against your own schema, get comfortable reading EXPLAIN ANALYZE output, and joins will stop feeling like a hurdle and start feeling like the most natural way to think about relational data.