[Solved] Consider this query intended to find pairs of employees who share the same manager:SELECT e1.name, e2.name FROM employees e1 JOIN employees e2 ON e1.manager_id = e2.manager_id WHERE e1.id != e2.... | PostgreSQL

PostgreSQL - Joins in PostgreSQL

Consider this query intended to find pairs of employees who share the same manager:

SELECT e1.name, e2.name
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.manager_id
WHERE e1.id != e2.id;

What is the main issue with this query?

AIt will return duplicate pairs in both orders (e.g., (Alice, Bob) and (Bob, Alice))

BIt will not return any rows because of the WHERE clause

CIt causes a syntax error due to missing aliases

DIt only returns employees with no manager

Step-by-Step Solution

Solution:

Step 1: Analyze join condition
The join matches employees with the same manager_id, including reversed pairs.
Step 2: Check WHERE clause effect
WHERE e1.id != e2.id avoids self-pairing but does not prevent duplicate pairs in reverse order.
Final Answer:
It will return duplicate pairs in both orders (e.g., (Alice, Bob) and (Bob, Alice)) -> Option A
Quick Check:
Self join can produce reversed duplicate pairs [OK]

Quick Trick: Self join pairs may appear twice reversed; use filtering [OK]

Common Mistakes:

Master "Joins in PostgreSQL" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

Consider this query intended to find pairs of employees who share the same manager: