The Components of Recursive CTEs

A recursive CTE comprises two core components:

1. Anchor Part: This is the initial query that serves as the starting point for the recursion.
2. Recursive Part: This segment references the anchor part and executes iteratively, utilizing the outcome of one iteration as the input for the next.

If this seems complex at first, don't worry; practical examples will clarify these concepts.

Working with Hierarchical Data

Hierarchical data, characterized by parent-child relationships, is prevalent in organizational structures, like an employee hierarchy. Each parent node can have multiple child nodes, and recursive CTEs can efficiently traverse such structures.

For example, consider an employee table from which you want to generate a comma-separated list of employees, representing the path from manager to subordinate.

DROP TABLE IF EXISTS analyticswithsuraj.employee;

CREATE TABLE analyticswithsuraj.employee (

EmployeeID VARCHAR(10),

EmployeeName VARCHAR(50),

ManagerID VARCHAR(10)

);

-- Insert sample data

INSERT INTO analyticswithsuraj.employee VALUES (1, 'John', NULL);

INSERT INTO analyticswithsuraj.employee VALUES (2, 'Jane', 1);

INSERT INTO analyticswithsuraj.employee VALUES (3, 'Bob', 1);

INSERT INTO analyticswithsuraj.employee VALUES (4, 'Alice', 2);

INSERT INTO analyticswithsuraj.employee VALUES (5, 'Charlie', 2);

INSERT INTO analyticswithsuraj.employee VALUES (6, 'David', 3);

INSERT INTO analyticswithsuraj.employee VALUES (7, 'Eva', 3);

Now, let's construct the recursive CTE to represent this hierarchy.

WITH RECURSIVE RecursiveCTE AS (

SELECT

EmployeeID,

EmployeeName,

ManagerID,

EmployeeName AS Path

FROM

analyticswithsuraj.employee

WHERE

ManagerID IS NULL -- Anchor part

UNION ALL

SELECT

e.EmployeeID,

e.EmployeeName,

e.ManagerID,

CONCAT_WS(',', rc.Path, e.EmployeeName) AS Path

FROM

analyticswithsuraj.employee e

JOIN

RecursiveCTE rc ON e.ManagerID = rc.EmployeeID -- Recursive part

)

This query will yield a comprehensive view of each employee's hierarchy.

The first video titled "Our Most Power Query Yet! Recursive CTE's (Common Table Expressions) | Essential SQL" explains the fundamentals of recursive CTEs, illustrating their power in SQL.

Analyzing Network Data

Network data consists of interconnected entities, resembling nodes (people or items) linked by edges (relationships). This structure is often seen in social networks, such as Facebook.

Imagine analyzing a network where you want to identify all connections related to a user named Paolo. The following SQL query creates a table representing these connections:

DROP TABLE IF EXISTS alldata.network_connections;

CREATE TABLE alldata.network_connections (

source_node VARCHAR(50),

target_node VARCHAR(50)

);

INSERT INTO alldata.network_connections (source_node, target_node) VALUES

('Paolo', 'David'),

('Paolo', 'Anna'),

('David', 'Mark'),

('Anna', 'Peter'),

('Samar', 'Patrik'),

('Mark', 'Vivan'),

('Patrik', 'Maya'),

('Julia', 'Robert');

Now, you can construct a recursive CTE to explore Paolo's network.

WITH RECURSIVE NetworkCTE AS (

SELECT source_node, target_node

FROM network_connections

WHERE source_node = 'Paolo'

UNION ALL

SELECT nc.source_node, nc.target_node

FROM network_connections nc

JOIN NetworkCTE n ON nc.source_node = n.target_node

)

SELECT * FROM NetworkCTE;

This query will display Paolo's direct friends and their connections.

The second video, "How do SQL Recursive CTE queries work - YouTube," delves deeper into the mechanics of recursive CTEs, providing further clarity on their application.