LATERAL subqueries in PostgreSQL - Mini Project: Build & Apply

Using LATERAL Subqueries in PostgreSQL

📖 Scenario: You are managing a small bookstore database. You want to find the latest review for each book along with the book details.

🎯 Goal: Build a query using LATERAL subqueries to fetch each book's title and its most recent review.

📋 What You'll Learn

Create a books table with id and title columns.

Create a reviews table with book_id, review_text, and review_date columns.

Insert sample data into both tables.

Write a query using LATERAL to get each book's title and its latest review text.

💡 Why This Matters

🌍 Real World

Bookstores and many other businesses often need to combine main records with related latest or summary data efficiently.

💼 Career

Understanding LATERAL joins helps database developers and analysts write advanced queries for reporting and data analysis.

1

Create the books and reviews tables with sample data

Create a table called books with columns id (integer primary key) and title (text). Then create a table called reviews with columns book_id (integer), review_text (text), and review_date (date). Insert these exact rows into books: (1, 'The Great Gatsby'), (2, '1984'), (3, 'To Kill a Mockingbird'). Insert these exact rows into reviews: (1, 'Amazing story', '2024-01-10'), (1, 'Loved the characters', '2024-02-15'), (2, 'Thought-provoking', '2024-03-05'), (3, 'A classic', '2024-01-20'), (3, 'Very moving', '2024-04-01').

PostgreSQL

-- Create books and reviews tables and insert data
-- Your code here

Hint

Use CREATE TABLE statements for both tables and INSERT INTO for adding rows.

2

Set up a query to select all books

Write a SELECT query that retrieves all columns from the books table. Assign this query to a variable or prepare it as the base for the next step.

PostgreSQL

SELECT * FROM books;
-- Your code here

Hint

Use SELECT * FROM books; to get all books.

3

Use a LATERAL subquery to get the latest review for each book

Write a SELECT query that retrieves the title from books and the review_text from the latest review for that book. Use a LATERAL join with a subquery that selects the review_text and review_date from reviews where book_id matches books.id, ordered by review_date descending, limited to 1 row.

PostgreSQL

SELECT books.title, latest.review_text
FROM books
CROSS JOIN LATERAL (
  SELECT review_text, review_date
  FROM reviews
  WHERE reviews.book_id = books.id
  ORDER BY review_date DESC
  LIMIT 1
) AS latest;
-- Your code here

Hint

Use CROSS JOIN LATERAL with a subquery that selects the latest review per book.

4

Complete the query to handle books without reviews

Modify the query to use LEFT JOIN LATERAL instead of CROSS JOIN LATERAL so that books without any reviews still appear with NULL in the review column.

PostgreSQL

SELECT books.title, latest.review_text
FROM books
LEFT JOIN LATERAL (
  SELECT review_text, review_date
  FROM reviews
  WHERE reviews.book_id = books.id
  ORDER BY review_date DESC
  LIMIT 1
) AS latest ON true;
-- Your code here

Hint

Change CROSS JOIN LATERAL to LEFT JOIN LATERAL and add ON true to include books without reviews.

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