[Solved] Which of the following demonstrates the correct way to write a LATERAL join in PostgreSQL? — Ans: SELECT u.id, o.total FROM users u JOIN LATERAL (SELECT total FROM orders WHERE... | PostgreSQL

PostgreSQL - Joins in PostgreSQL

Which of the following demonstrates the correct way to write a LATERAL join in PostgreSQL?

ASELECT u.id, o.total FROM users u JOIN LATERAL (SELECT total FROM orders) o ON u.id = o.user_id;

BSELECT u.id, o.total FROM users u JOIN LATERAL orders o ON u.id = o.user_id;

CSELECT u.id, o.total FROM users u JOIN LATERAL orders ON orders.user_id = u.id;

DSELECT u.id, o.total FROM users u JOIN LATERAL (SELECT total FROM orders WHERE user_id = u.id) o ON true;

Step-by-Step Solution

Solution:

Step 1: Recall LATERAL syntax
LATERAL must be used with a subquery or function that can reference preceding tables.
Step 2: Evaluate options
SELECT u.id, o.total FROM users u JOIN LATERAL (SELECT total FROM orders WHERE user_id = u.id) o ON true; correctly uses a subquery referencing u.id with ON true. Options B and C incorrectly treat orders as a table without subquery. SELECT u.id, o.total FROM users u JOIN LATERAL (SELECT total FROM orders) o ON u.id = o.user_id; references u.id in ON clause but the subquery does not correlate.
Final Answer:
SELECT u.id, o.total FROM users u JOIN LATERAL (SELECT total FROM orders WHERE user_id = u.id) o ON true; is the correct syntax.
Quick Check:
Is the subquery correlated and ON clause correct? Yes. [OK]

Quick Trick: LATERAL needs a correlated subquery with ON true [OK]

Common Mistakes:

Master "Joins in PostgreSQL" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

Which of the following demonstrates the correct way to write a LATERAL join in PostgreSQL?