Join algorithms (nested loop, hash, merge) in PostgreSQL - Mini Project: Build & Apply

Explore Join Algorithms in PostgreSQL

📖 Scenario: You are working with a small bookstore database. The database has two tables: authors and books. Each book has an author_id that links it to an author. You want to learn how to join these tables using different join algorithms in PostgreSQL.

🎯 Goal: Build SQL queries that join the authors and books tables using nested loop join, hash join, and merge join algorithms. You will create the tables, insert data, and write queries that demonstrate each join type.

📋 What You'll Learn

Create authors and books tables with specified columns

Insert exact data into both tables

Write a query that uses a nested loop join

Write a query that uses a hash join

Write a query that uses a merge join

💡 Why This Matters

🌍 Real World

Database administrators and developers often need to understand how different join algorithms work to optimize query performance in real applications.

💼 Career

Knowing how to control and analyze join algorithms is important for roles like database developer, data engineer, and backend developer to write efficient SQL queries.

1

Create tables and insert data

Create a table called authors with columns author_id (integer primary key) and name (text). Create another table called books with columns book_id (integer primary key), title (text), and author_id (integer). Insert these exact rows into authors: (1, 'Jane Austen'), (2, 'Mark Twain'), (3, 'Charles Dickens'). Insert these exact rows into books: (101, 'Pride and Prejudice', 1), (102, 'Adventures of Huckleberry Finn', 2), (103, 'Oliver Twist', 3), (104, 'Emma', 1).

PostgreSQL

-- Create authors and books tables and insert data
-- Your code here

Need a hint?

Use CREATE TABLE statements for both tables. Then use INSERT INTO with multiple rows for inserting data.

2

Enable nested loop join

Set the PostgreSQL configuration to enable only nested loop joins by running SET enable_nestloop = on; and disable hash and merge joins by running SET enable_hashjoin = off; and SET enable_mergejoin = off;.

PostgreSQL

CREATE TABLE authors (
    author_id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE TABLE books (
    book_id INTEGER PRIMARY KEY,
    title TEXT,
    author_id INTEGER
);

INSERT INTO authors (author_id, name) VALUES
(1, 'Jane Austen'),
(2, 'Mark Twain'),
(3, 'Charles Dickens');

INSERT INTO books (book_id, title, author_id) VALUES
(101, 'Pride and Prejudice', 1),
(102, 'Adventures of Huckleberry Finn', 2),
(103, 'Oliver Twist', 3),
(104, 'Emma', 1);

-- Set join algorithm to nested loop only
-- Your code here

Need a hint?

Use SET commands to enable and disable join types.

3

Write a nested loop join query

Write a SQL query that joins authors and books on author_id using an explicit INNER JOIN. Select authors.name and books.title. Use the join algorithm settings from Step 2.

PostgreSQL

CREATE TABLE authors (
    author_id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE TABLE books (
    book_id INTEGER PRIMARY KEY,
    title TEXT,
    author_id INTEGER
);

INSERT INTO authors (author_id, name) VALUES
(1, 'Jane Austen'),
(2, 'Mark Twain'),
(3, 'Charles Dickens');

INSERT INTO books (book_id, title, author_id) VALUES
(101, 'Pride and Prejudice', 1),
(102, 'Adventures of Huckleberry Finn', 2),
(103, 'Oliver Twist', 3),
(104, 'Emma', 1);

SET enable_nestloop = on;
SET enable_hashjoin = off;
SET enable_mergejoin = off;

-- Write a query joining authors and books on author_id
-- Your code here

Need a hint?

Use SELECT with INNER JOIN and the ON clause to join tables.

4

Write hash join and merge join queries

First, set PostgreSQL to enable only hash joins by running SET enable_hashjoin = on;, and disable nested loop and merge joins by running SET enable_nestloop = off; and SET enable_mergejoin = off;. Then write the same join query as Step 3. Next, set PostgreSQL to enable only merge joins by running SET enable_mergejoin = on;, and disable nested loop and hash joins by running SET enable_nestloop = off; and SET enable_hashjoin = off;. Then write the same join query again.

PostgreSQL

CREATE TABLE authors (
    author_id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE TABLE books (
    book_id INTEGER PRIMARY KEY,
    title TEXT,
    author_id INTEGER
);

INSERT INTO authors (author_id, name) VALUES
(1, 'Jane Austen'),
(2, 'Mark Twain'),
(3, 'Charles Dickens');

INSERT INTO books (book_id, title, author_id) VALUES
(101, 'Pride and Prejudice', 1),
(102, 'Adventures of Huckleberry Finn', 2),
(103, 'Oliver Twist', 3),
(104, 'Emma', 1);

SET enable_nestloop = on;
SET enable_hashjoin = off;
SET enable_mergejoin = off;

SELECT authors.name, books.title
FROM authors
INNER JOIN books ON authors.author_id = books.author_id;

-- Now enable hash join only and write the join query
-- Your code here

-- Then enable merge join only and write the join query
-- Your code here

Need a hint?

Use SET commands to switch join algorithms, then write the same join query each time.