LATERAL subqueries in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of a LATERAL join with filtering

Given the tables authors and books, what is the output of the following query?

SELECT a.name, b.title FROM authors a CROSS JOIN LATERAL (SELECT title FROM books WHERE author_id = a.id ORDER BY published_year DESC LIMIT 1) b ORDER BY a.id;

Assume authors has 3 rows with ids 1, 2, 3 and books has multiple books per author.

PostgreSQL

SELECT a.name, b.title FROM authors a CROSS JOIN LATERAL (SELECT title FROM books WHERE author_id = a.id ORDER BY published_year DESC LIMIT 1) b ORDER BY a.id;

A [{"name": "Author1", "title": "Latest Book A1"}, {"name": "Author2", "title": "Latest Book A2"}, {"name": "Author3", "title": "Latest Book A3"}]

B [{"name": "Author1", "title": "Oldest Book A1"}, {"name": "Author2", "title": "Oldest Book A2"}, {"name": "Author3", "title": "Oldest Book A3"}]

C [{"name": "Author1", "title": null}, {"name": "Author2", "title": null}, {"name": "Author3", "title": null}]

D [{"name": "Author1", "title": "Latest Book A3"}, {"name": "Author2", "title": "Latest Book A2"}, {"name": "Author3", "title": "Latest Book A1"}]

💡 Hint

LATERAL allows the subquery to use columns from the current row of the main query.

The LATERAL subquery selects the latest book per author by ordering books by published_year descending and limiting to 1. The CROSS JOIN LATERAL applies this for each author row, producing the latest book title per author.

📝 Syntax

intermediate

Identify the syntax error in LATERAL usage

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

PostgreSQL

SELECT a.name, b.title FROM authors a JOIN LATERAL (SELECT title FROM books WHERE author_id = a.id) b ON true;

A SELECT a.name, b.title FROM authors a JOIN LATERAL (SELECT title FROM books WHERE author_id = a.id) b ON true;

B SELECT a.name, b.title FROM authors a CROSS JOIN LATERAL (SELECT title FROM books WHERE author_id = a.id) b;

C SELECT a.name, b.title FROM authors a JOIN LATERAL SELECT title FROM books WHERE author_id = a.id b ON true;

D SELECT a.name, b.title FROM authors a LEFT JOIN LATERAL (SELECT title FROM books WHERE author_id = a.id) b ON true;

💡 Hint

Check the parentheses around the subquery after LATERAL.

Option C is missing parentheses around the subquery after LATERAL, causing a syntax error. LATERAL must be followed by a subquery enclosed in parentheses.

❓ optimization

advanced

Optimizing a query with LATERAL for performance

You have a query that uses LATERAL to find the top 3 recent orders per customer:

SELECT c.customer_id, o.order_id FROM customers c JOIN LATERAL (SELECT order_id FROM orders WHERE customer_id = c.customer_id ORDER BY order_date DESC LIMIT 3) o ON true;

Which change will most likely improve performance on large datasets?

A Add an index on orders(customer_id, order_date DESC)

B Rewrite the query using a correlated subquery in SELECT clause

C Remove the LIMIT clause to allow full scan

D Use CROSS JOIN instead of JOIN LATERAL

💡 Hint

Indexes help speed up filtering and ordering.

Adding a composite index on orders(customer_id, order_date DESC) helps the database quickly find the top 3 recent orders per customer, improving query performance.

🔧 Debug

advanced

Debugging unexpected NULL results in LATERAL join

A query uses LEFT JOIN LATERAL to get the latest comment per post:

SELECT p.post_id, c.comment_text FROM posts p LEFT JOIN LATERAL (SELECT comment_text FROM comments WHERE post_id = p.post_id ORDER BY created_at DESC LIMIT 1) c ON true;

Some posts have NULL for comment_text even though comments exist. What is the most likely cause?

A The subquery does not filter correctly because post_id is ambiguous

B The ON true condition causes the join to fail for some rows

C The LEFT JOIN LATERAL returns NULL when no matching comments exist

D The comments table has no matching post_id values due to data mismatch

💡 Hint

Check data consistency between posts and comments tables.

If some posts have NULL comment_text despite comments existing, it likely means the comments have post_id values that do not match the posts table due to data inconsistency or mismatch.

🧠 Conceptual

expert

Understanding LATERAL subquery execution order

Consider the query:

SELECT t1.id, t2.val FROM t1 JOIN LATERAL (SELECT val FROM t2 WHERE t2.ref_id = t1.id ORDER BY val DESC LIMIT 1) t2 ON true;

Which statement best describes how the LATERAL subquery executes?

A The subquery runs after the entire join is completed

B The subquery runs once per t1 row, using that row's id to filter t2

C The subquery runs once and uses all t1 rows to filter t2

D The subquery runs independently of t1 and returns all t2 rows

💡 Hint

LATERAL allows the subquery to reference columns from the outer query row.

LATERAL subqueries execute once per row of the outer query (t1), using that row's columns (t1.id) to filter the inner query (t2). This allows correlated filtering per row.

Practice

1. What is the main purpose of using LATERAL in a PostgreSQL query?

easy

A. To allow a subquery to reference columns from tables listed before it in the FROM clause

B. To create a temporary table that lasts only for the duration of the query

C. To optimize the query by parallelizing subqueries automatically

D. To enforce foreign key constraints between tables

Solution

1. Step 1: Understand the role of LATERAL

   LATERAL allows a subquery to access columns from tables that appear earlier in the FROM clause.

2. Step 2: Compare with other options

   The other options describe different features unrelated to LATERAL's purpose.

3. Final Answer:

   To allow a subquery to reference columns from tables listed before it in the FROM clause -> Option A

4. Quick Check:

   LATERAL = subquery uses previous table columns [OK]

Hint: LATERAL lets subqueries see earlier tables in FROM [OK]

Common Mistakes:

• Thinking LATERAL creates temporary tables
• Confusing LATERAL with query parallelism
• Assuming LATERAL enforces constraints

2. Which of the following is the correct syntax to use a LATERAL subquery in PostgreSQL?

easy

A. SELECT * FROM table1, (SELECT * FROM table2 WHERE table2.id = table1.id) sub;

B. SELECT * FROM table1 JOIN LATERAL table2 ON table2.id = table1.id;

C. SELECT * FROM table1 CROSS JOIN LATERAL (SELECT * FROM table2 WHERE table2.id = table1.id) sub;

D. SELECT * FROM table1 LATERAL JOIN table2 ON table2.id = table1.id;

Solution

1. Step 1: Identify correct LATERAL syntax

   The correct syntax uses CROSS JOIN LATERAL with a subquery in parentheses and an alias.

2. Step 2: Check each option

   SELECT * FROM table1, (SELECT * FROM table2 WHERE table2.id = table1.id) sub; misses LATERAL keyword causing reference error; SELECT * FROM table1 JOIN LATERAL table2 ON table2.id = table1.id; uses JOIN LATERAL incorrectly without parentheses; SELECT * FROM table1 LATERAL JOIN table2 ON table2.id = table1.id; has invalid syntax.

3. Final Answer:

   SELECT * FROM table1 CROSS JOIN LATERAL (SELECT * FROM table2 WHERE table2.id = table1.id) sub; -> Option C

4. Quick Check:

   Correct LATERAL syntax = SELECT * FROM table1 CROSS JOIN LATERAL (SELECT * FROM table2 WHERE table2.id = table1.id) sub; [OK]

Hint: Use CROSS JOIN LATERAL with subquery in parentheses [OK]

Common Mistakes:

• Omitting LATERAL keyword
• Not aliasing the subquery
• Using LATERAL without parentheses

3. Given tables users(id, name) and orders(user_id, amount), what does this query return?

SELECT u.name, o.total FROM users u CROSS JOIN LATERAL (SELECT SUM(amount) AS total FROM orders WHERE user_id = u.id) o;

medium

A. Each user with the total amount of their orders

B. All users joined with all orders without filtering

C. Only users who have orders, with their order totals

D. Syntax error due to missing GROUP BY

Solution

1. Step 1: Analyze the LATERAL subquery

   The subquery sums orders per user using user_id = u.id, so it calculates total per user.

2. Step 2: Understand CROSS JOIN LATERAL effect

   It runs the subquery for each user, returning their name and total order amount, including users with zero orders (total will be NULL).

3. Final Answer:

   Each user with the total amount of their orders -> Option A

4. Quick Check:

   LATERAL sums orders per user = Each user with the total amount of their orders [OK]

Hint: LATERAL runs subquery per row, here summing orders per user [OK]

Common Mistakes:

• Thinking it returns all orders without grouping
• Assuming only users with orders appear
• Confusing CROSS JOIN LATERAL with INNER JOIN

4. Identify the error in this query using LATERAL:

SELECT t1.id, t2.val FROM table1 t1 JOIN LATERAL (SELECT val FROM table2 WHERE table2.ref = t1.id) t2;

medium

A. Missing alias for the LATERAL subquery

B. JOIN LATERAL requires CROSS keyword

C. Subquery must return only one column

D. No error; query is correct

Solution

1. Step 1: Check alias usage

   The subquery is aliased as t2, so no issue there.

2. Step 2: Verify JOIN LATERAL syntax

   JOIN LATERAL without CROSS is valid syntax in PostgreSQL; CROSS JOIN LATERAL is one form, but JOIN LATERAL alone is allowed as INNER JOIN LATERAL.

3. Step 3: Confirm subquery column count

   Subquery returns one column 'val', which is acceptable.

4. Final Answer:

   No error; query is correct -> Option D

5. Quick Check:

   JOIN LATERAL syntax without CROSS is valid = No error [OK]

Hint: JOIN LATERAL without CROSS is valid syntax [OK]

Common Mistakes:

• Using JOIN LATERAL without CROSS
• Forgetting subquery alias
• Expecting subquery to return multiple columns

5. You want to list each customer with their top 2 most expensive orders using LATERAL. Which query correctly achieves this?

SELECT c.name, o.order_id, o.amount FROM customers c CROSS JOIN LATERAL (SELECT order_id, amount FROM orders WHERE customer_id = c.id ORDER BY amount DESC LIMIT 2) o;

hard

A. The query misses GROUP BY on customers

B. This query correctly lists each customer with their top 2 orders

C. LATERAL cannot be used with LIMIT inside subqueries

D. The query will return only customers with at least 2 orders

Solution

1. Step 1: Understand LATERAL with LIMIT

   The subquery selects top 2 orders per customer by ordering and limiting results.

2. Step 2: Check if query returns all customers

   CROSS JOIN LATERAL runs subquery per customer; if no orders, no rows for that customer appear.

3. Step 3: Confirm correctness

   The query correctly lists each customer with up to 2 highest orders; no syntax or logic errors.

4. Final Answer:

   This query correctly lists each customer with their top 2 orders -> Option B

5. Quick Check:

   LATERAL with LIMIT works for top N per row [OK]

Hint: Use LATERAL with ORDER BY and LIMIT for top N per row [OK]

Common Mistakes:

• Thinking GROUP BY is needed here
• Believing LATERAL disallows LIMIT
• Assuming all customers appear even without orders