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.