Multiple SELECT in a single query (hierarchical query) [duplicate]

Question

I have a comments table with the following content

id	parent_id	content
1	NULL	A
2	NULL	B
3	NULL	C
4	1	AA
5	1	AB
6	1	AC
7	5	ABA
8	5	ABB

And I want to select comment with id = 1, all of it children and grand children. Something like this

id	parent_id	content
1	NULL	A
4	1	AA
5	1	AB
6	1	AC
7	5	ABA
8	5	ABB

For now I use the following query:

SELECT * FROM comments WHERE id = 1
UNION
SELECT * FROM comments WHERE parent_id IN (SELECT id FROM comments WHERE id = 1)
UNION
SELECT * FROM comments WHERE parent_id IN (SELECT id FROM comments WHERE parent_id IN (SELECT id FROM comments WHERE id = 1));

But this solution can quickly become a huge mess and error prone. Especially when I also want to get the deeper children like grand grand grand children

So my question is there any way to make this query simpler? And maybe more performant?

Answer 1

On MySQL 8+, we can use a recursive CTE:

WITH RECURSIVE cte (id, parent_id, content) AS (
    SELECT id, parent_id, content FROM comments WHERE id = 1  -- base case
    UNION ALL
    SELECT c.id, c.parent_id, c.content                       -- recursive case
    FROM comments c
    INNER JOIN cte t ON c.parent_id = t.id
)

SELECT * FROM cte ORDER BY id;