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.