Solution:Step 1: Understand the LATERAL subqueryThe subquery selects the highest order amount per user by ordering descending and limiting to 1.Step 2: Analyze the JOIN LATERAL effectFor each user row, the subquery runs and returns that user's top order amount, joining on true to include all users w

[Solved] Given the tables:users(id, name)orders(id, user_id, amount)What will this query return?SELECT u.name, o.amount FROM users u JOIN LATERAL (SELECT amount FROM orders WHERE user_id = u.... | PostgreSQL

PostgreSQL - Joins in PostgreSQL

Given the tables:
users(id, name)
orders(id, user_id, amount)
What will this query return?

SELECT u.name, o.amount FROM users u JOIN LATERAL (SELECT amount FROM orders WHERE user_id = u.id ORDER BY amount DESC LIMIT 1) o ON true;

AUsers with all their orders listed

BAll orders with user names repeated

CSyntax error due to missing ON condition

DEach user with their highest order amount

Step-by-Step Solution

Solution:

Step 1: Understand the LATERAL subquery
The subquery selects the highest order amount per user by ordering descending and limiting to 1.
Step 2: Analyze the JOIN LATERAL effect
For each user row, the subquery runs and returns that user's top order amount, joining on true to include all users with orders.
Final Answer:
Each user with their highest order amount -> Option D
Quick Check:
LATERAL subquery returns top order per user [OK]

Quick Trick: LATERAL runs subquery per user to get top order [OK]

Common Mistakes:

Thinking it returns all orders per user
Assuming syntax error due to ON true
Confusing join with CROSS JOIN

Master "Joins in PostgreSQL" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

Given the tables:

Step 1: Understand the LATERAL subquery

Step 2: Analyze the JOIN LATERAL effect

Final Answer:

Quick Check:

Want More Practice?