Common Table Expressions (CTEs)

1. Non-Recursive CTE (Simplest Example)

A non-recursive CTE is a straightforward CTE where the result is generated once without any self-referencing. Here's a simple example that uses a CTE to retrieve a subset of data from a table.

Example: Non-Recursive CTE to Filter Employees

-- Creating a simple employees table for demonstration
CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    name VARCHAR2(50),
    salary NUMBER
);

-- Inserting sample data
INSERT INTO employees (employee_id, name, salary) VALUES (1, 'Alice', 5000);
INSERT INTO employees (employee_id, name, salary) VALUES (2, 'Bob', 3000);
INSERT INTO employees (employee_id, name, salary) VALUES (3, 'Charlie', 6000);

-- Non-Recursive CTE to filter employees earning more than 4000
WITH high_salary_employees AS (
    SELECT employee_id, name, salary
    FROM employees
    WHERE salary > 4000
)
SELECT * FROM high_salary_employees;

Explanation:

• The CTE high_salary_employees selects employees with a salary greater than 4000.
• The main query retrieves data from this temporary result set.

2. Recursive CTE (Simplest Example)

A recursive CTE is one where the CTE references itself in the query. This is often used for hierarchical data or situations where you need to repeat a process until a condition is met.

Example: Recursive CTE to Generate a Sequence of Numbers

-- Recursive CTE to generate a sequence of numbers from 1 to 5

WITH RECURSIVE MY_CTE AS (

  select 1 as n    ---Base Query

  UNION ALL

  SELECT n+1 from MY_CTE  -- Recursive QUERY
  where n<5          --- Condition check

)
SELECT * from MY_CTE

Explanation:

• The anchor member starts with the number 1.
• The recursive member repeatedly adds 1 to the previous result until it reaches 5.
• The query generates a simple sequence of numbers from 1 to 5.

Summary:

Non-recursive CTEs: Used to simplify complex queries by breaking them into logical parts (e.g., filtering or aggregating data).

Recursive CTEs: Used to handle hierarchical or iterative problems (e.g., generating sequences, traversing trees).

These are the simplest examples of CTEs, showcasing both recursive and non-recursive uses.