When should I use a recursive CTE or set operation?

Definition:

A recursive CTE repeatedly extends an initial result to walk relationships such as a manager hierarchy. Set operations combine compatible result sets by keeping all rows, common rows or differences. They solve different needs: recursion follows links, while set operations compare or combine complete results.

Key points:

• Recursive CTE: walk a hierarchy, such as employee → manager.

• UNION removes duplicate result rows; UNION ALL keeps them.

• INTERSECT finds common rows; EXCEPT finds rows missing from the second result.

• Recursive queries need a termination condition or cycle handling when the data can contain loops.

Interview point:

• UNION removes duplicates, which costs work; use UNION ALL when duplicates are valid or already impossible.