When to use Common Table Expression (CTE)?

Question

I have begun reading about *Common Table Expression* and cannot think of a use case where I would need to use them. They would seem to be redundant as the same can be done with derived tables. Is there something I am missing or not understanding well? Can someone give me a simple example of limitations with regular select, derived or temp table queries to make the case of CTE? Any simple examples would be highly appreciated.

Answer 1

I see two reasons when CTE will be useful:

1-When you have recursion you can apply to your data .

Simple example is factorial asashown below:

WITH Factorial (operand,total_so_far)

AS

(SELECT 5 operand, 5 total_so_far FROM dual -- Using anchor member to pass in "5"

UNION ALL

-- Recursive member.

SELECT operand-1, total_so_far * (operand-1) FROM Factorial

WHERE operand > 1)

SELECT * FROM Factorial;

Another example where you have tree like structure with root and child nodes:

WITH t1(id, parent_id) AS (

-- Anchor member.

SELECT id,

parent_id

FROM tab1

WHERE parent_id IS NULL

UNION ALL

-- Recursive member.

SELECT t2.id,

t2.parent_id

FROM tab1 t2, t1

WHERE t2.parent_id = t1.id

)

SELECT id,

parent_id

FROM t1;

2- Second reason is to simplify readability of complex subqueries. Itis easaier to follow the sql logic with CTE if the virtual view has multi JOINs and/or this view need to be used in multiple times in your pl/sql block for instance.

Hope this helps!