Join algorithms (nested loop, hash, merge) in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to perform a nested loop join between tables employees and departments on department_id.

PostgreSQL

SELECT e.name, d.name FROM employees e [1] JOIN departments d ON e.department_id = d.department_id;

Drag options to blanks, or click blank then click option'

AMERGE

BNESTED LOOP

CHASH

DINNER

Attempts:

3 left

💡 Hint

Common Mistakes

Trying to specify the join algorithm directly in the SQL query.

Using unsupported keywords like NESTED LOOP in the JOIN clause.

In SQL, the keyword INNER JOIN is used to join tables. The actual join algorithm (nested loop, hash, merge) is chosen by the database engine internally and is not specified in the query.

2fill in blank

medium

Complete the EXPLAIN command to check the query plan for a hash join between employees and departments.

PostgreSQL

EXPLAIN [1] SELECT e.name, d.name FROM employees e JOIN departments d ON e.department_id = d.department_id;

Drag options to blanks, or click blank then click option'

AANALYZE

BVERBOSE

CCOSTS

DBUFFERS

Attempts:

3 left

💡 Hint

Common Mistakes

Using EXPLAIN without ANALYZE to see actual join algorithms.

Using unrelated EXPLAIN options that don't run the query.

The EXPLAIN ANALYZE command runs the query and shows the actual execution plan including timing and join algorithms like hash join.

3fill in blank

hard

Fix the error in the query that tries to force a merge join by setting the join type incorrectly.

PostgreSQL

SET join_collapse_limit = 1; SET enable_hashjoin = off; SET enable_nestloop = off; SET enable_mergejoin = on; SELECT * FROM employees e [1] JOIN departments d ON e.department_id = d.department_id;

Drag options to blanks, or click blank then click option'

AINNER

BMERGE

CHASH

DNESTED LOOP

Attempts:

3 left

💡 Hint

Common Mistakes

Trying to write MERGE JOIN as a join type in the SQL query.

Confusing join algorithm with join type keywords.

The join type in the SQL query should be INNER JOIN. The database decides the join algorithm (merge join here) based on the settings, not the join keyword.

4fill in blank

hard

Fill both blanks to create a hash join query that selects employee names and department names where department_id matches.

PostgreSQL

SELECT e.name, d.name FROM employees e [1] JOIN departments d ON e.department_id [2] d.department_id;

Drag options to blanks, or click blank then click option'

AINNER

B=

C<>

DLEFT

Attempts:

3 left

💡 Hint

Common Mistakes

Using LEFT JOIN when INNER JOIN is needed.

Using <> instead of = in the join condition.

The correct join type is INNER JOIN and the join condition uses the equality operator = to match department IDs.

5fill in blank

hard

Fill all three blanks to write a query that forces a nested loop join by disabling other join types and selecting employee and department names.

PostgreSQL

SET enable_hashjoin = [1]; SET enable_mergejoin = [2]; SET enable_nestloop = [3]; SELECT e.name, d.name FROM employees e INNER JOIN departments d ON e.department_id = d.department_id;

Drag options to blanks, or click blank then click option'

Aon

Boff

Ctrue

Dfalse

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'true' or 'false' instead of 'on' or 'off'.

Not disabling both hash and merge joins.

To force a nested loop join, disable hash join and merge join by setting them to off, and enable nested loop join by setting it to on.

Practice

1. Which join algorithm in PostgreSQL is best suited for small tables or when one table is very small compared to the other?

easy

A. Index Join

B. Hash Join

C. Nested Loop Join

D. Merge Join

Solution

1. Step 1: Understand Nested Loop Join usage

   Nested Loop Join works by scanning one table and for each row scanning the other table. It is efficient when one table is small.

2. Step 2: Compare with other joins

   Hash Join is better for large unsorted tables, Merge Join requires sorted inputs. Nested Loop is simplest and best for small tables.

3. Final Answer:

   Nested Loop Join -> Option C

4. Quick Check:

   Small table + Nested Loop Join = best [OK]

Hint: Small table joins usually use Nested Loop Join [OK]

Common Mistakes:

• Confusing Hash Join as best for small tables
• Thinking Merge Join works well without sorted data
• Assuming Index Join is a separate join algorithm

2. Which of the following is the correct syntax to hint PostgreSQL to use a Hash Join in a query?

easy

A. SELECT /*+ HashJoin */ * FROM table1 JOIN table2 ON table1.id = table2.id;

B. SET enable_hashjoin = on; SELECT * FROM table1 JOIN table2 ON table1.id = table2.id;

C. SELECT * FROM table1 HASH JOIN table2 ON table1.id = table2.id;

D. SELECT * FROM table1 JOIN table2 USING HASH(id);

Solution

1. Step 1: Understand PostgreSQL join hints

   PostgreSQL does not support inline join hints like /*+ HashJoin */ or HASH JOIN syntax.

2. Step 2: Use configuration to enable Hash Join

   We can enable or disable join types using SET commands, e.g., SET enable_hashjoin = on; before the query.

3. Final Answer:

   SET enable_hashjoin = on; SELECT ... -> Option B

4. Quick Check:

   PostgreSQL uses SET to enable join types [OK]

Hint: Use SET enable_hashjoin to control hash join usage [OK]

Common Mistakes:

• Using Oracle-style hints like /*+ HashJoin */
• Trying to write HASH JOIN in SQL syntax
• Using USING HASH() which is invalid

3. Given two tables employees(emp_id, dept_id) and departments(dept_id, name), what join algorithm will PostgreSQL most likely use for this query?

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

Assuming both tables are large and departments.dept_id is indexed.

medium

A. Nested Loop Join

B. Merge Join

C. Cross Join

D. Hash Join

Solution

1. Step 1: Analyze table sizes and indexes

   Both tables are large, so Nested Loop is inefficient. Departments has an index on dept_id.

2. Step 2: Determine join algorithm choice

   Hash Join is preferred for large tables without sorted data. Merge Join requires sorted inputs, which is not guaranteed here.

3. Final Answer:

   Hash Join -> Option D

4. Quick Check:

   Large tables + no sorted data = Hash Join [OK]

Hint: Large tables with join keys use Hash Join by default [OK]

Common Mistakes:

• Assuming index forces Nested Loop Join
• Thinking Merge Join is automatic without sorting
• Confusing Cross Join with inner join

4. You wrote this query:

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

But PostgreSQL is using a Nested Loop Join causing slow performance. Which fix will most likely improve performance by enabling a better join algorithm?

medium

A. Disable Nested Loop Join with SET enable_nestloop = off;

B. Create an index on orders.customer_id

C. Rewrite query using LEFT JOIN instead of JOIN

D. Add ORDER BY on customer_id in the query

Solution

1. Step 1: Identify why Nested Loop is slow

   Nested Loop is slow on large tables without indexes or when better joins exist but are not chosen.

2. Step 2: Force PostgreSQL to avoid Nested Loop

   Disabling Nested Loop join with SET enable_nestloop = off forces PostgreSQL to pick Hash or Merge Join, improving performance.

3. Final Answer:

   Disable Nested Loop Join with SET enable_nestloop = off; -> Option A

4. Quick Check:

   Disable Nested Loop to force better join [OK]

Hint: Disable nested loop join to force hash or merge join [OK]

Common Mistakes:

• Assuming adding index always fixes join choice
• Changing JOIN type without understanding join algorithms
• Adding ORDER BY does not affect join algorithm

5. You have two large sorted tables sales(date, product_id, amount) and products(product_id, name). You want to join them on product_id efficiently. Which join algorithm should you prefer and why?

hard

A. Merge Join, because it exploits sorted order for fast merging

B. Hash Join, because it hashes the smaller table for fast lookup

C. Nested Loop Join, because it works well with sorted data

D. Cross Join, because it combines all rows

Solution

1. Step 1: Identify join algorithm suited for sorted tables

   Merge Join is designed to efficiently join two sorted inputs by merging them in order.

2. Step 2: Compare with other join algorithms

   Nested Loop is inefficient for large tables, Hash Join ignores sorting, Cross Join produces Cartesian product.

3. Final Answer:

   Merge Join, because it exploits sorted order for fast merging -> Option A

4. Quick Check:

   Sorted tables + Merge Join = efficient join [OK]

Hint: Use Merge Join when both tables are sorted on join keys [OK]

Common Mistakes:

• Choosing Nested Loop for large sorted tables
• Ignoring sorting and picking Hash Join
• Confusing Cross Join with inner join