[Solved] Consider these tables:departments(id, name)employees(id, dept_id, salary)What does this query return?SELECT d.name, e.... | PostgreSQL

PostgreSQL - Joins in PostgreSQL

Consider these tables:
departments(id, name)
employees(id, dept_id, salary)
What does this query return?

SELECT d.name, e.salary FROM departments d JOIN LATERAL (SELECT salary FROM employees WHERE dept_id = d.id ORDER BY salary DESC LIMIT 1) e ON true;

ADepartments with employees earning below average

BEach department with its highest employee salary

CAll employees with their department names

DDepartments without employees

Step-by-Step Solution

Solution:

Step 1: Analyze the LATERAL subquery
It selects the highest salary per department by ordering salaries descending and limiting to 1.
Step 2: Understand the join effect
Each department joins to one employee salary: the highest in that department.
Final Answer:
Each department with its highest employee salary -> Option B
Quick Check:
LATERAL with ORDER BY and LIMIT 1 returns top value per group [OK]

Quick Trick: Use LATERAL with ORDER BY and LIMIT 1 for top per group [OK]

Common Mistakes:

Master "Joins in PostgreSQL" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

Consider these tables: