[Solved] Given the table categories(id, name, parent_id) with data:(1, 'Electronics', NULL), (2, 'Computers', 1), (3, 'Laptops', 2)What will the following query return?... | PostgreSQL

PostgreSQL - Common Table Expressions

Given the table categories(id, name, parent_id) with data:
(1, 'Electronics', NULL), (2, 'Computers', 1), (3, 'Laptops', 2)
What will the following query return?

WITH RECURSIVE cat_tree AS ( SELECT id, name, parent_id FROM categories WHERE parent_id IS NULL UNION ALL SELECT c.id, c.name, c.parent_id FROM categories c JOIN cat_tree ct ON c.parent_id = ct.id ) SELECT * FROM cat_tree ORDER BY id;

AAll categories with their hierarchy starting from 'Electronics'

BOnly categories with NULL parent_id

CCategories without any children

DAn empty result set

Step-by-Step Solution

Solution:

Step 1: Analyze anchor member
The anchor selects categories with NULL parent_id, which is 'Electronics' (id=1).
Step 2: Analyze recursive member
It joins categories whose parent_id matches ids in cat_tree, so it adds 'Computers' (parent 1) and then 'Laptops' (parent 2).
Final Answer:
All categories with their hierarchy starting from 'Electronics' -> Option A
Quick Check:
Recursive CTE returns full hierarchy [OK]

Quick Trick: Recursive join builds full hierarchy from root [OK]

Common Mistakes:

Thinking only root rows are returned
Assuming only leaf nodes are returned
Expecting empty results due to recursion

Master "Common Table Expressions" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

Given the table categories(id, name, parent_id) with data:

Step 1: Analyze anchor member

Step 2: Analyze recursive member

Final Answer:

Quick Check:

Want More Practice?