LATERAL join for correlated subqueries in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of LATERAL join with correlated subquery

Given the tables departments and employees, what is the output of this query?

SELECT d.name, e.name
FROM departments d
JOIN LATERAL (SELECT name FROM employees WHERE department_id = d.id ORDER BY salary DESC LIMIT 1) e ON true
ORDER BY d.id;

PostgreSQL

CREATE TABLE departments (id INT PRIMARY KEY, name TEXT);
CREATE TABLE employees (id INT PRIMARY KEY, name TEXT, department_id INT, salary INT);

INSERT INTO departments VALUES (1, 'HR'), (2, 'Engineering');
INSERT INTO employees VALUES (1, 'Alice', 1, 50000), (2, 'Bob', 1, 60000), (3, 'Charlie', 2, 70000), (4, 'Diana', 2, 65000);

A [{"department": "HR", "employee": "Bob"}, {"department": "Engineering", "employee": "Diana"}]

B [{"department": "HR", "employee": "Alice"}, {"department": "Engineering", "employee": "Charlie"}]

C [{"department": "HR", "employee": "Alice"}, {"department": "Engineering", "employee": "Diana"}]

D [{"department": "HR", "employee": "Bob"}, {"department": "Engineering", "employee": "Charlie"}]

💡 Hint

Look for the employee with the highest salary in each department.

The LATERAL join runs the subquery for each department row. It selects the employee with the highest salary in that department. For HR, Bob has the highest salary (60000). For Engineering, Charlie has the highest salary (70000).

📝 Syntax

intermediate

Identify the syntax error in LATERAL join usage

Which option contains a syntax error when using LATERAL join in PostgreSQL?

PostgreSQL

SELECT d.name, e.name FROM departments d JOIN LATERAL (SELECT name FROM employees WHERE department_id = d.id) e ON true;

A SELECT d.name, e.name FROM departments d JOIN LATERAL SELECT name FROM employees WHERE department_id = d.id e ON true;

B SELECT d.name, e.name FROM departments d JOIN LATERAL (SELECT name FROM employees WHERE department_id = d.id) e ON true;

C SELECT d.name, e.name FROM departments d JOIN LATERAL (SELECT name FROM employees WHERE department_id = d.id) AS e ON true;

D SELECT d.name, e.name FROM departments d JOIN LATERAL (SELECT name FROM employees WHERE department_id = d.id) e ON d.id = e.department_id;

💡 Hint

Check if the subquery is properly enclosed in parentheses.

Option A misses parentheses around the subquery after LATERAL, which is required syntax. The others correctly use parentheses or aliasing.

❓ optimization

advanced

Optimizing a LATERAL join query for performance

You have this query:

SELECT d.name, e.name
FROM departments d
JOIN LATERAL (SELECT name FROM employees WHERE department_id = d.id ORDER BY salary DESC LIMIT 1) e ON true;

Which change will most likely improve performance on large tables?

A Add an index on employees(department_id, salary DESC)

B Remove the ORDER BY clause inside the subquery

C Replace LATERAL join with a CROSS JOIN

D Add a WHERE clause filtering departments by name

💡 Hint

Think about how the database finds the top salary employee per department efficiently.

An index on employees(department_id, salary DESC) helps the database quickly find the highest salary employee per department without scanning all employees.

🔧 Debug

advanced

Why does this LATERAL join query return no rows?

Given these tables:

departments(id, name), employees(id, name, department_id, salary INT)

Why does this query return no rows?

SELECT d.name, e.name
FROM departments d
JOIN LATERAL (SELECT name FROM employees WHERE department_id = d.id AND salary > 100000) e ON true;

A The lateral subquery must not have a WHERE clause referencing outer table columns

B JOIN LATERAL requires ON condition to compare columns from both tables, ON true is invalid

C No employees have salary > 100000, so the lateral subquery returns no rows, causing JOIN to behave like INNER JOIN

D The query is missing GROUP BY clause for aggregation

💡 Hint

Consider what happens when the lateral subquery returns no rows in a JOIN.

No employees have salary > 100000, so for every department the lateral subquery returns no rows. Since it's a JOIN (inner join), departments without matching employees are excluded, resulting in no rows.

🧠 Conceptual

expert

Understanding LATERAL join behavior with multiple rows

Consider this query:

SELECT d.name, e.name
FROM departments d
JOIN LATERAL (SELECT name FROM employees WHERE department_id = d.id LIMIT 2) e ON true
ORDER BY d.id, e.name;

How many rows will this query return if each department has 3 employees?

A 3 rows per department, total rows = number_of_departments * 3

B 2 rows per department, total rows = number_of_departments * 2

C 1 row per department, total rows = number_of_departments

D number_of_departments rows, but only employees with highest salary

💡 Hint

LATERAL runs the subquery for each row of the left table and returns all rows from the subquery.

The subquery limits to 2 employees per department, so for each department row, 2 employee rows are joined, resulting in 2 rows per department.