LATERAL subqueries in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a LATERAL subquery in PostgreSQL?

A LATERAL subquery is a special kind of subquery that can refer to columns of tables that appear before it in the FROM clause. It allows the subquery to use values from preceding tables, enabling row-by-row processing.

intermediate

How does a LATERAL subquery differ from a regular subquery in the FROM clause?

A regular subquery in the FROM clause cannot access columns from tables listed before it. A LATERAL subquery can access those columns, making it possible to join each row of the preceding table with a set of rows returned by the subquery.

intermediate

Write a simple example of a LATERAL subquery to find the top 2 orders per customer.

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

advanced

Why is LATERAL useful for queries involving functions returning sets?

LATERAL allows you to call set-returning functions for each row of a preceding table, using that row's data as input. This enables dynamic and row-specific function calls within a query.

intermediate

Can you use LATERAL with JOIN syntax? If yes, how?

Yes, you can use LATERAL with JOIN. For example: SELECT * FROM customers c LEFT JOIN LATERAL (SELECT * FROM orders WHERE customer_id = c.customer_id LIMIT 1) o ON true; This joins each customer with their first order if any.

What does the LATERAL keyword allow a subquery to do?

A Access columns from preceding tables in the FROM clause

B Run faster than regular subqueries

C Return only one row

D Execute outside the main query

LATERAL allows the subquery to refer to columns from tables that appear before it in the FROM clause.

Which of the following is a valid use of LATERAL in PostgreSQL?

A SELECT * FROM table1 WHERE LATERAL (SELECT * FROM table2);

B SELECT * FROM table1, LATERAL (SELECT * FROM table2 WHERE table2.id = table1.id);

C SELECT LATERAL * FROM table1;

D LATERAL SELECT * FROM table1;

LATERAL is used in the FROM clause to allow the subquery to access preceding tables.

What happens if you omit LATERAL when the subquery needs to reference a preceding table?

A The query will fail with an error

B The subquery will run independently without error

C The subquery will automatically become LATERAL

D The query will return empty results

Without LATERAL, the subquery cannot reference preceding tables and will cause an error.

Which JOIN type can be combined with LATERAL subqueries?

A JOIN cannot be used with LATERAL

B LEFT JOIN only

C INNER JOIN only

D Any JOIN type including INNER, LEFT, RIGHT

LATERAL can be used with any JOIN type to join each row with a subquery result.

In the query: SELECT c.id, o.* FROM customers c, LATERAL (SELECT * FROM orders WHERE customer_id = c.id LIMIT 1) o; what does the subquery do?

A Returns all orders for all customers

B Returns orders unrelated to customers

C Returns one order per customer using the customer's id

D Returns no rows

The subquery returns one order per customer by using the customer's id from the preceding table.

Explain in your own words what a LATERAL subquery is and why it is useful.

Think about how subqueries normally work and what LATERAL changes.

Describe a scenario where using a LATERAL subquery would solve a problem that a regular subquery cannot.

Consider queries that need to join each row with a dynamic set of rows.

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