# Recursive queries in MySQL 8 using CTEs

## Common Table Expressions (CTEs)

Common Table Expressions (CTEs) are a powerful feature in SQL that allows you to define a temporary result set within the context of a single SELECT, INSERT, UPDATE, or DELETE statement. CTEs can simplify complex queries and make them easier to read and maintain.

In MySQL 8, CTEs are implemented using the WITH clause, followed by a SELECT statement. The WITH clause defines the CTE and the SELECT statement uses it as a derived table.

Here's a simple example of a CTE in MySQL 8:

```sql
WITH cte AS (
    SELECT *
    FROM mytable
    WHERE mycolumn = 'somevalue'
)
SELECT *
FROM cte
```

In this example, the CTE `cte` is defined to be the result of a SELECT statement that retrieves all rows from the `mytable` table where `mycolumn` is equal to `'somevalue'`. The SELECT statement that follows the WITH clause then retrieves all rows from the CTE.

CTEs can be used in a variety of ways, including breaking down complex queries into smaller, more manageable parts, improving readability and maintainability of SQL code, and improving performance by avoiding unnecessary subqueries and temporary tables.

## Recursive CTE

CTEs can be used in recursive queries, where a query references itself until a certain condition is met.

```sql
WITH RECURSIVE cte (n) AS (
    SELECT 1

    UNION ALL
    SELECT n + 1
    FROM cte
    WHERE n < 10
)
SELECT n FROM cte;
```

This query creates a CTE named `cte` that starts with a base case of `1`, and then repeatedly adds `1` to the previous result until the value reaches `10`. The final query then selects all values in the `cte`.

## Using Recursive CTE to query a graph data model

With the recursive capability, we can make a query that traverses a graph-like schema. Let's say we have 2 tables `nodes` and `edges`

```sql
CREATE TABLE nodes (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE edges (
    from_id INT NOT NULL,
    to_id INT NOT NULL,
    connection VARCHAR(20) NOT NULL,
    PRIMARY KEY (from_id, to_id),
    FOREIGN KEY (from_id) REFERENCES nodes (id),
    FOREIGN KEY (to_id) REFERENCES nodes (id)
);
```

`nodes` are connected through `edges` table where `from_id` and `to_id` columns refer to a `nodes` record. Furthermore, let's say `edges` have a connection type using `connection` where values could be either "primary" or "secondary".

Let's say we have 5 `nodes` and their corresponding connections:

```sql
INSERT INTO nodes (id, name)
VALUES
    (1, 'Node 1'),
    (2, 'Node 2'),
    (3, 'Node 3'),
    (4, 'Node 4'),
    (5, 'Node 5');

INSERT INTO edges (from_id, to_id, connection)
VALUES
    (1, 2, 'primary'),
    (1, 3, 'secondary'),
    (2, 4, 'primary'),
    (4, 5, 'secondary');
```

To retrieve all primary connections from node id = 1, a CTE can be used to retrieve the edges that are connected to node 1, and then recursively retrieve all the nodes that are connected to node 1 through primary connection.

```sql
WITH RECURSIVE connected_nodes (id) AS (
    SELECT n.id
    FROM nodes n
    JOIN edges e ON e.from_id = n.id
    WHERE n.id = 1 AND e.connection = 'primary'

    UNION ALL
    SELECT n.id
    FROM connected_nodes c
    JOIN edges e ON e.from_id = c.id
    JOIN nodes n ON n.id = e.to_id
    WHERE e.connection = 'primary'
)
SELECT id, name
FROM nodes n
WHERE id IN (SELECT id FROM connected_nodes)
```

This query uses a CTE `connected_nodes` to traverse the graph of connected nodes starting from `Node id = 1` and only following edges with `connection = 'primary'`. The CTE has columns `id`. The first SELECT statement retrieves the nodes that are directly connected to `Node id = 1` via a primary edge.

The second SELECT statement uses the UNION ALL operator to combine the results of the previous SELECT statement with a new set of nodes connected to the previous set of nodes via a primary edge.

The final SELECT statement retrieves the names of all nodes that are part of the CTE `connected_nodes`. The result of this query will be Nodes with id 1, 2 and 4.

### Conclusion

The use of Common Table Expressions (CTEs) can greatly enhance the functionality of SQL queries in MySQL 8. CTEs offer a way to simplify complex recursive queries by breaking them down into smaller, more manageable parts, making them easier to read and maintain. With the use of the WITH clause and the SELECT statement, CTEs can be used to efficiently solve problems that involve hierarchical or tree-like data structures. Overall, CTEs are a valuable tool for any MySQL 8 developer to have in their toolkit.