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.

Practice

1. What is the main purpose of using a self join in PostgreSQL?

easy

A. To join a table to itself to compare rows within the same table

B. To join two different tables based on a common column

C. To create a backup of a table

D. To delete duplicate rows from a table

Solution

1. Step 1: Understand the concept of self join

   A self join is used when you want to compare rows within the same table by joining the table to itself.

2. Step 2: Differentiate from other joins

   Other joins combine different tables, but self join works on one table with aliases to distinguish instances.

3. Final Answer:

   To join a table to itself to compare rows within the same table -> Option A

4. Quick Check:

   Self join = join table to itself [OK]

Hint: Self join means joining table to itself using aliases [OK]

Common Mistakes:

• Confusing self join with joining two different tables
• Thinking self join creates a new table
• Assuming self join deletes duplicates

2. Which of the following is the correct syntax to perform a self join on a table named employees with aliases e1 and e2?

easy

A. SELECT * FROM employees e1 LEFT JOIN employees e2 ON e1.id = e2.manager_id;

B. SELECT * FROM employees JOIN employees ON employees.manager_id = employees.id;

C. SELECT * FROM employees AS e1, employees AS e2 WHERE e1.id = e2.id;

D. SELECT * FROM employees e1 JOIN employees e2 ON e1.manager_id = e2.id;

Solution

1. Step 1: Identify correct alias usage

   Using aliases e1 and e2 for the same table employees is necessary to distinguish instances.

2. Step 2: Check join condition

   The join condition e1.manager_id = e2.id correctly links employees to their managers.

3. Final Answer:

   SELECT * FROM employees e1 JOIN employees e2 ON e1.manager_id = e2.id; -> Option D

4. Quick Check:

   Use aliases and proper ON condition [OK]

Hint: Use aliases and ON clause for self join syntax [OK]

Common Mistakes:

• Not using aliases causing ambiguity
• Wrong join condition columns
• Using comma join without ON clause

3. Given the table employees(id, name, manager_id) with data:

id | name  | manager_id
---+-------+-----------
1  | Alice | NULL
2  | Bob   | 1
3  | Carol | 1
4  | Dave  | 2

What will be the output of this query?

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

medium

A. [{"employee": "Alice", "manager": null}, {"employee": "Bob", "manager": null}, {"employee": "Carol", "manager": null}, {"employee": "Dave", "manager": null}]

B. [{"employee": "Alice", "manager": null}, {"employee": "Bob", "manager": "Alice"}, {"employee": "Carol", "manager": "Alice"}, {"employee": "Dave", "manager": "Bob"}]

C. [{"employee": "Alice", "manager": "Alice"}, {"employee": "Bob", "manager": "Bob"}, {"employee": "Carol", "manager": "Carol"}, {"employee": "Dave", "manager": "Dave"}]

D. [{"employee": "Alice", "manager": "Bob"}, {"employee": "Bob", "manager": "Carol"}, {"employee": "Carol", "manager": "Dave"}, {"employee": "Dave", "manager": null}]

Solution

1. Step 1: Understand the join logic

   The LEFT JOIN matches each employee's manager_id to the manager's id, returning manager's name or null if none.

2. Step 2: Map each employee to their manager

   Alice has no manager (null), Bob and Carol have Alice as manager, Dave has Bob as manager.

3. Final Answer:

   [{"employee": "Alice", "manager": null}, {"employee": "Bob", "manager": "Alice"}, {"employee": "Carol", "manager": "Alice"}, {"employee": "Dave", "manager": "Bob"}] -> Option B

4. Quick Check:

   LEFT JOIN matches manager_id to id [OK]

Hint: Match manager_id to id with LEFT JOIN for manager names [OK]

Common Mistakes:

• Confusing employee and manager columns
• Using INNER JOIN losing employees without managers
• Assuming manager_id is employee id

4. 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?

medium

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

B. It will not return any rows because of the WHERE clause

C. It causes a syntax error due to missing aliases

D. It only returns employees with no manager

Solution

1. Step 1: Analyze join condition

   The join matches employees with the same manager_id, including reversed pairs.

2. Step 2: Check WHERE clause effect

   WHERE e1.id != e2.id avoids self-pairing but does not prevent duplicate pairs in reverse order.

3. Final Answer:

   It will return duplicate pairs in both orders (e.g., (Alice, Bob) and (Bob, Alice)) -> Option A

4. Quick Check:

   Self join can produce reversed duplicate pairs [OK]

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

Common Mistakes:

• Assuming WHERE clause removes duplicates
• Thinking query causes syntax error
• Believing it returns only employees without managers

5. You want to find all employees who are managers themselves, i.e., employees who have at least one other employee reporting to them. Which self join query correctly achieves this?

hard

A. SELECT e1.name FROM employees e1 JOIN employees e2 ON e1.manager_id = e2.id;

B. SELECT e1.name FROM employees e1 LEFT JOIN employees e2 ON e1.id = e2.manager_id WHERE e2.id IS NULL;

C. SELECT DISTINCT e1.name FROM employees e1 JOIN employees e2 ON e1.id = e2.manager_id;

D. SELECT e1.name FROM employees e1 WHERE e1.manager_id IS NOT NULL;

Solution

1. Step 1: Understand the requirement

   Managers are employees whose id appears as manager_id for others.

2. Step 2: Analyze each option

   SELECT DISTINCT e1.name FROM employees e1 JOIN employees e2 ON e1.id = e2.manager_id; joins employees where e1.id = e2.manager_id, selecting distinct managers correctly.

3. Final Answer:

   SELECT DISTINCT e1.name FROM employees e1 JOIN employees e2 ON e1.id = e2.manager_id; -> Option C

4. Quick Check:

   Join on id = manager_id finds managers [OK]

Hint: Join employees where id = manager_id to find managers [OK]

Common Mistakes:

• Using LEFT JOIN with IS NULL returns non-managers
• Joining on wrong columns (manager_id = id reversed)
• Filtering on manager_id IS NOT NULL returns employees with managers