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

Need a 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

Need a 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

Need a 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

Need a hint?

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