How Joins Work in PostgreSQL

Joins are one of the most powerful features in SQL, allowing you to combine data from multiple tables in a single query. PostgreSQL, as a relational database system, provides robust support for different types of joins. Understanding how joins work under the hood helps you write more efficient queries and troubleshoot performance issues.

What Is a Join?

A join combines rows from two or more tables based on a related column between them. This is commonly used to retrieve meaningful data that is stored in normalized structures.

Example:

SELECT o.order_id, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

This retrieves a list of orders along with the corresponding customer name.

Types of Joins in PostgreSQL

1. Inner Join

Returns rows when there is a match in both tables.

SELECT * FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id;

2. Left Join (Left Outer Join)

Returns all rows from the left table, and the matched rows from the right table. NULLs are returned if there’s no match.

SELECT * FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id;

3. Right Join (Right Outer Join)

Returns all rows from the right table, and the matched rows from the left table.

SELECT * FROM orders o
RIGHT JOIN customers c ON o.customer_id = c.customer_id;

4. Full Outer Join

Returns rows when there is a match in either left or right table.

SELECT * FROM orders o
FULL OUTER JOIN customers c ON o.customer_id = c.customer_id;

5. Cross Join

Returns the Cartesian product of both tables.

SELECT * FROM orders o
CROSS JOIN customers c;

How PostgreSQL Executes Joins

PostgreSQL uses different algorithms to execute joins depending on data size, indexing, and query structure:

1. Nested Loop Join

Best for small datasets or when indexes are available.
Loops through one table and probes the other for matches.

2. Hash Join

Good for large datasets.
Builds a hash table of one table in memory and probes it with the other.

3. Merge Join

Efficient when both inputs are sorted on the join keys.
Merges the two sorted tables.

You can inspect how a join is executed using:

EXPLAIN SELECT ... FROM ... JOIN ...;

Or with execution stats:

EXPLAIN ANALYZE SELECT ...;

Example EXPLAIN Output

EXPLAIN ANALYZE
SELECT o.order_id, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

Might return:

Nested Loop  (cost=0.56..10.75 rows=5 width=64)
  ->  Index Scan using idx_orders_customer_id on orders o
  ->  Index Scan using idx_customers_id on customers c

This shows PostgreSQL using a Nested Loop with Index Scans.

Best Practices

Always use indexed columns for joins.
Avoid joining large tables without filters.
Use EXPLAIN to understand the join strategy.
Reduce data as early as possible (filter before join).

Conclusion

Joins are critical for working with relational data. PostgreSQL offers a range of join types and optimizes execution based on the query context. Understanding how joins work — and how PostgreSQL chooses execution plans — helps you write faster, more reliable queries.

What’s your favorite join type or optimization trick? Drop a comment below!