[Solved] What will be the output of this query?SELECT u.id, o.amount FROM users u LEFT JOIN LATERAL (SELECT amount FROM orders WHERE user_id = u.... | PostgreSQL

PostgreSQL - Subqueries in PostgreSQL

What will be the output of this query?

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

Assuming user 1 has orders 10, 20 and user 2 has no orders.

ASyntax error due to ON true

BUser 1 with amount 20, User 2 excluded

CUser 1 and 2 both with amount 10

DUser 1 with amount 10, User 2 with NULL amount

Step-by-Step Solution

Solution:

Step 1: Understand LEFT JOIN LATERAL with ON true
LEFT JOIN LATERAL applies the subquery per user; ON true means join always attempts.
Step 2: Analyze data and output
User 1 has orders 10 and 20; subquery picks lowest amount (ORDER BY amount LIMIT 1) = 10. User 2 has no orders, so amount is NULL.
Final Answer:
User 1 with amount 10, User 2 with NULL amount -> Option D
Quick Check:
LEFT JOIN LATERAL returns NULL if no match [OK]

Quick Trick: LEFT JOIN LATERAL returns NULL for no matching rows [OK]

Common Mistakes:

Master "Subqueries in PostgreSQL" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

What will be the output of this query?