Join algorithms (nested loop, hash, merge) in PostgreSQL

Introduction

Joins combine rows from two tables based on related columns to get useful combined information.

When you want to see matching data from two lists, like customers and their orders.

When you need to combine employee info with their department details.

When you want to find pairs of products and their suppliers.

When you want to compare two sets of data to find common or different entries.

Syntax

PostgreSQL

SELECT columns
FROM table1
JOIN table2 ON table1.column = table2.column;

The JOIN keyword combines rows from two tables based on a condition.

The ON clause specifies which columns to match between the tables.

Examples

This joins customers with their orders using customer ID.

PostgreSQL

SELECT * FROM customers
JOIN orders ON customers.id = orders.customer_id;

This joins employees with their department details.

PostgreSQL

SELECT * FROM employees
JOIN departments ON employees.dept_id = departments.id;

Sample Program

This example creates two tables, inserts data, and runs a join to show students with their scores. EXPLAIN ANALYZE shows which join algorithm PostgreSQL uses.

PostgreSQL

CREATE TABLE students (id INT, name TEXT);
CREATE TABLE scores (student_id INT, score INT);
CREATE INDEX scores_student_id_idx ON scores(student_id);

INSERT INTO students VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Carol');
INSERT INTO scores VALUES (1, 85), (2, 90), (4, 75);

EXPLAIN ANALYZE
SELECT students.name, scores.score
FROM students
JOIN scores ON students.id = scores.student_id;

Important Notes

PostgreSQL chooses the join algorithm automatically based on data size and indexes.

Nested loop join works well for small tables or when one table is very small.

Hash join is faster for large tables without useful indexes.

Merge join requires both tables to be sorted on the join columns.

Summary

Joins combine rows from two tables based on matching columns.

PostgreSQL uses nested loop, hash, or merge join algorithms depending on data.

Understanding join types helps optimize query speed.