Self join patterns in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Find employees and their managers using self join

Given the employees table with columns id, name, and manager_id (which references id of the manager), what is the output of the following query?

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
ORDER BY e.id;

PostgreSQL

CREATE TABLE employees (id INT PRIMARY KEY, name TEXT, manager_id INT);
INSERT INTO employees VALUES
(1, 'Alice', NULL),
(2, 'Bob', 1),
(3, 'Charlie', 1),
(4, 'Diana', 2);

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
ORDER BY e.id;

A [{"employee": "Alice", "manager": "Alice"}, {"employee": "Bob", "manager": "Bob"}, {"employee": "Charlie", "manager": "Charlie"}, {"employee": "Diana", "manager": "Diana"}]

B [{"employee": "Alice", "manager": null}, {"employee": "Bob", "manager": "Alice"}, {"employee": "Charlie", "manager": "Alice"}, {"employee": "Diana", "manager": "Bob"}]

C [{"employee": "Alice", "manager": "Bob"}, {"employee": "Bob", "manager": "Charlie"}, {"employee": "Charlie", "manager": "Diana"}, {"employee": "Diana", "manager": null}]

D [{"employee": "Alice", "manager": null}, {"employee": "Bob", "manager": null}, {"employee": "Charlie", "manager": null}, {"employee": "Diana", "manager": null}]

💡 Hint

Think about how the manager_id links to the id of the manager in the same table.

The query joins the employees table to itself to find each employee's manager by matching manager_id to id. Alice has no manager, so manager is null. Bob and Charlie report to Alice, and Diana reports to Bob.

📝 Syntax

intermediate

Identify the syntax error in self join query

Which option contains a syntax error in this self join query 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;

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

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

C SELECT e1.name, e2.name FROM employees e1, employees e2 WHERE e1.manager_id = e2.manager_id AND e1.id != e2.id;

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

💡 Hint

Check if the JOIN clause has a proper ON condition.

Option A is missing the ON keyword after JOIN employees e2, which causes a syntax error. The WHERE clause cannot replace the ON condition in an explicit JOIN.

❓ optimization

advanced

Optimize self join query for hierarchical data

You want to find all employees and their direct managers from a large employees table. Which query is more efficient?

A SELECT e.name, m.name FROM employees e JOIN employees m ON e.manager_id = m.id;

B SELECT e.name, (SELECT name FROM employees WHERE id = e.manager_id) AS manager FROM employees e;

C SELECT e.name, m.name FROM employees e CROSS JOIN employees m WHERE e.manager_id = m.id;

D SELECT e.name, m.name FROM employees e LEFT JOIN employees m ON e.manager_id = m.id;

💡 Hint

Consider the difference between LEFT JOIN and JOIN and the impact on rows returned.

Option D uses a LEFT JOIN to include employees without managers (e.g., top-level). It is efficient and returns all employees. Option D uses a correlated subquery which can be slower on large tables. Option D excludes employees without managers. Option D uses CROSS JOIN with a WHERE filter, which is less efficient.

🧠 Conceptual

advanced

Understanding recursive self join for hierarchy

Which statement best describes how recursive self joins help in querying hierarchical data?

A Recursive self joins repeatedly join a table to itself to traverse multiple levels of hierarchy until a condition is met.

B Recursive self joins join two different tables repeatedly to combine unrelated data.

C Recursive self joins use a single join to fetch all hierarchy levels at once without iteration.

D Recursive self joins are not used for hierarchical data but for combining unrelated datasets.

💡 Hint

Think about how you can find all subordinates under a manager at any depth.

Recursive self joins (often implemented with recursive CTEs) join a table to itself repeatedly to walk down or up a hierarchy until all levels are processed.

🔧 Debug

expert

Debug incorrect self join query output

Given the employees table with id, name, and manager_id, a query attempts to list employees and their managers but returns duplicate rows for some employees. Which option explains the cause?

SELECT e.name AS employee, m.name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.id
JOIN employees d ON d.manager_id = m.id
WHERE d.name = 'Diana';

A The query is missing GROUP BY, which is required to remove duplicates.

B The WHERE clause filters employees incorrectly, causing duplicates in the output.

C The extra join to 'd' causes multiple rows per employee if a manager has multiple subordinates, leading to duplicates.

D The join condition is incorrect; it should use LEFT JOIN instead of JOIN to avoid duplicates.

💡 Hint

Consider how joining the same table multiple times can multiply rows.

The join to d finds all employees managed by the same manager as e. If a manager has multiple subordinates, this join multiplies rows for each combination, causing duplicates.