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.