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:

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.

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

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:

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.

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.