[Solved] What does this query return?SELECT e.name, s.max_sale FROM employees e CROSS JOIN LATERAL (SELECT MAX(total_sale) AS max_sale FROM sales WHERE employee_id = e.id) s; | PostgreSQL

PostgreSQL - Subqueries in PostgreSQL

Consider tables employees(id, name) and sales(employee_id, total_sale). What does this query return?

SELECT e.name, s.max_sale FROM employees e CROSS JOIN LATERAL (SELECT MAX(total_sale) AS max_sale FROM sales WHERE employee_id = e.id) s;

AAll employees paired with every sale they made, resulting in multiple rows per employee.

BEach employee's name with their highest sale amount, including employees with no sales showing NULL.

COnly employees who have made sales, showing their highest sale amount.

DA list of all sales with employee names repeated for each sale.

Step-by-Step Solution

Solution:

Step 1: Understand CROSS JOIN LATERAL
The LATERAL subquery runs for each employee, calculating MAX(total_sale) for that employee.
Step 2: Analyze the subquery result
MAX(total_sale) returns NULL if no sales exist for that employee.
Step 3: Result interpretation
The query returns each employee's name with their highest sale or NULL if none.
Final Answer:
Each employee's name with their highest sale amount, including employees with no sales showing NULL. correctly describes the output.
Quick Check:
CROSS JOIN LATERAL runs subquery per row, preserving all employees [OK]

Quick Trick: CROSS JOIN LATERAL preserves all rows, subquery can return NULL [OK]

Common Mistakes:

Master "Subqueries in PostgreSQL" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

Consider tables employees(id, name) and sales(employee_id, total_sale). What does this query return?