INNER JOIN execution in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of INNER JOIN with matching keys

Given two tables Employees and Departments, what is the output of the following query?

SELECT Employees.name, Departments.department_name FROM Employees INNER JOIN Departments ON Employees.department_id = Departments.id;

Assume:
Employees: (id:1, name:'Alice', department_id:10), (id:2, name:'Bob', department_id:20), (id:3, name:'Charlie', department_id:30)
Departments: (id:10, department_name:'HR'), (id:20, department_name:'Finance')

PostgreSQL

SELECT Employees.name, Departments.department_name FROM Employees INNER JOIN Departments ON Employees.department_id = Departments.id;

A [{"name": "Alice", "department_name": "HR"}, {"name": "Bob", "department_name": "Finance"}]

B [{"name": "Alice", "department_name": "HR"}, {"name": "Bob", "department_name": "Finance"}, {"name": "Charlie", "department_name": null}]

C [{"name": "Charlie", "department_name": "HR"}]

D []

💡 Hint

INNER JOIN returns rows where the join condition matches in both tables.

The INNER JOIN returns only rows where Employees.department_id matches Departments.id. Charlie's department_id 30 has no matching department, so he is excluded.

🧠 Conceptual

intermediate

Understanding INNER JOIN behavior

Which statement best describes the behavior of an INNER JOIN between two tables?

A It returns all rows from the second table and matching rows from the first table, including unmatched rows as NULLs.

B It returns all rows from the first table and matching rows from the second table, including unmatched rows as NULLs.

C It returns all rows from both tables, combining them regardless of matching values.

D It returns only rows where there is a matching value in both tables based on the join condition.

💡 Hint

Think about which rows appear when both tables have matching keys.

INNER JOIN returns only rows where the join condition matches in both tables, excluding unmatched rows.

📝 Syntax

advanced

Identify the syntax error in INNER JOIN query

Which option contains a syntax error in the INNER JOIN query?

PostgreSQL

SELECT e.name, d.department_name FROM Employees e INNER JOIN Departments d ON e.department_id = d.id;

A SELECT e.name, d.department_name FROM Employees e INNER JOIN Departments d ON e.department_id = d.id ORDER BY e.name;

B SELECT e.name, d.department_name FROM Employees e INNER JOIN Departments d ON e.department_id = d.id LIMIT 5;

C SELECT e.name, d.department_name FROM Employees e INNER JOIN Departments d WHERE e.department_id = d.id;

D SELECT e.name, d.department_name FROM Employees e INNER JOIN Departments d ON e.department_id = d.id;

💡 Hint

INNER JOIN requires ON clause for join condition, not WHERE.

Option C uses WHERE instead of ON for the join condition, causing syntax error.

❓ optimization

advanced

Optimizing INNER JOIN performance

Which option is the best way to optimize INNER JOIN performance on large tables?

A Add indexes on the columns used in the join condition in both tables.

B Use a CROSS JOIN instead of INNER JOIN for faster results.

C Use SELECT * to retrieve all columns to avoid specifying columns.

D Avoid using WHERE clause filters after the INNER JOIN.

💡 Hint

Indexes help the database find matching rows faster.

Adding indexes on join columns speeds up matching rows lookup, improving INNER JOIN performance.

🔧 Debug

expert

Diagnose unexpected INNER JOIN result

A query uses INNER JOIN between tables Orders and Customers on Customers.id = Orders.customer_id. The result is empty, but both tables have data. What is the most likely cause?

A The database engine does not support INNER JOIN.

B The join condition uses wrong columns or mismatched data types causing no matches.

C INNER JOIN always returns empty if one table has more rows than the other.

D The SELECT statement is missing a WHERE clause to filter rows.

💡 Hint

Check if the join columns actually match and have compatible data types.

If join columns do not match or have incompatible types, no rows join and result is empty.