How Can I Perform Recursive Queries in SQLite3?

Recursive Queries in SQLite3: A Comprehensive Guide

While SQLite3 has historically lacked support for recursive queries, recent advancements have introduced this capability with the introduction of common table expressions (CTEs) in version 3.8.3.

With Recursive CTEs (SQLite 3.8.3 or Higher)

SQLite3 now offers the WITH statement, which allows for the definition of recursive CTEs. Using a recursive CTE, you can easily perform recursive queries:

WITH RECURSIVE Subparts AS (
    SELECT Part, SuperPart
    FROM Part
    WHERE SuperPart IS NULL
    UNION
    SELECT Part, SuperPart
    FROM Subparts
    JOIN Part
    ON Subparts.SuperPart = Part.Part
)
SELECT *
FROM Subparts;

Recursive Queries in Earlier SQLite3 Versions

Prior to version 3.8.3, SQLite3 did not support recursive queries. However, a workaround exists by implementing the recursion in the client code:

1. Initial Step: Retrieve the initial row and the sub-part IDs.

SELECT Part, SuperPart
FROM Part
WHERE SuperPart IS NULL;

2. Recursive Step: Recursively retrieve rows and sub-part IDs for the sub-parts.

SELECT Part, SuperPart
FROM Part
WHERE SuperPart IN (sub-part IDs from previous step);

3. Stop Condition: Continue the recursion until no more sub-parts are found.

The above is the detailed content of How Can I Perform Recursive Queries in SQLite3?. For more information, please follow other related articles on the PHP Chinese website!