Join algorithms (nested loop, hash, merge) in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a Nested Loop Join in SQL?

A Nested Loop Join compares each row of one table with each row of another table to find matching rows. It works like checking every item in one list against every item in another list.

intermediate

How does a Hash Join work?

A Hash Join builds a fast lookup table (hash table) from one table's join column, then scans the other table to find matches quickly using the hash table.

intermediate

What is the main requirement for a Merge Join to work efficiently?

Both tables must be sorted on the join columns. Merge Join then walks through both tables in order, matching rows like merging two sorted lists.

advanced

When is Nested Loop Join preferred over Hash or Merge Join?

When one table is very small or when indexes exist on join columns, Nested Loop Join can be faster because it avoids building extra structures.

intermediate

Why might a Hash Join be faster than a Nested Loop Join?

Because Hash Join uses a hash table to quickly find matching rows, it avoids checking every pair of rows, making it faster for large tables without indexes.

Which join algorithm requires both tables to be sorted on the join keys?

A Cross Join

B Hash Join

C Nested Loop Join

D Merge Join

Merge Join works efficiently only if both tables are sorted on the join columns.

What is the main data structure used in a Hash Join?

A Hash Table

B Array

C Linked List

D Binary Tree

Hash Join builds a hash table from one table's join column to quickly find matching rows.

When is Nested Loop Join usually the best choice?

A When one table is small or indexed

B When both tables are large and unsorted

C When tables are sorted

D When no join condition exists

Nested Loop Join is efficient when one table is small or when indexes exist on join columns.

Which join algorithm can be slowest for large tables without indexes?

A Hash Join

B Merge Join

C Nested Loop Join

D Index Join

Nested Loop Join checks every pair of rows, which can be very slow for large tables without indexes.

What is a key advantage of Merge Join over Hash Join?

A Works without sorting

B Uses less memory

C Faster on unsorted data

D Builds hash tables

Merge Join uses less memory because it processes sorted data sequentially without building hash tables.

Explain how Nested Loop Join, Hash Join, and Merge Join differ in their approach to joining tables.

Think about how each method finds matching rows.

Describe scenarios where each join algorithm (Nested Loop, Hash, Merge) is most efficient.

Consider table size and sorting.