Solution:Step 1: Analyze the subqueryThe subquery selects amount from orders where customer_id = c.id. If multiple orders exist per customer, multiple rows are returned.Step 2: Understand JOIN behaviorJOIN expects one row per join key unless using JOIN LATERAL with LIMIT or aggregation.Step 3: Ident

[Solved] Examine this query:SELECT c.name, o.amount FROM customers c JOIN LATERAL (SELECT amount FROM orders WHERE customer_id = c.id) o ON true;What is the likely cause of an error? | PostgreSQL

PostgreSQL - Joins in PostgreSQL

Examine this query:

SELECT c.name, o.amount FROM customers c JOIN LATERAL (SELECT amount FROM orders WHERE customer_id = c.id) o ON true;

What is the likely cause of an error?

AThe subquery returns multiple rows without LIMIT, causing ambiguity.

BThe ON clause is missing a join condition referencing both tables.

CThe LATERAL keyword cannot be used with JOIN.

DThe subquery in LATERAL does not have an alias.

Step-by-Step Solution

Solution:

Step 1: Analyze the subquery
The subquery selects amount from orders where customer_id = c.id. If multiple orders exist per customer, multiple rows are returned.
Step 2: Understand JOIN behavior
JOIN expects one row per join key unless using JOIN LATERAL with LIMIT or aggregation.
Step 3: Identify error cause
Without LIMIT or aggregation, the subquery returns multiple rows, causing an error.
Final Answer:
The subquery returns multiple rows without LIMIT, causing ambiguity. -> Option A
Quick Check:
Does the subquery return multiple rows? Yes. [OK]

Quick Trick: Subquery must return one row per outer row [OK]

Common Mistakes:

Assuming ON clause must have join condition referencing both tables
Thinking LATERAL cannot be used with JOIN
Forgetting to alias the subquery (which is done here)

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

Examine this query:

Step 1: Analyze the subquery

Step 2: Understand JOIN behavior

Step 3: Identify error cause

Final Answer:

Quick Check:

Want More Practice?