如何使用SQL在数据库中检测具有嵌套关系的父级？

CREATE TABLE parent (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE child (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    parent_id INT,
    FOREIGN KEY (parent_id) REFERENCES parent(id)
);

INSERT INTO parent (id, name) VALUES (1, 'Parent 1');
INSERT INTO parent (id, name) VALUES (2, 'Parent 2');
INSERT INTO child (id, name, parent_id) VALUES (1, 'Child 1', 1);
INSERT INTO child (id, name, parent_id) VALUES (2, 'Child 2', 1);
INSERT INTO child (id, name, parent_id) VALUES (3, 'Child 3', 2);

SELECT p.id AS parent_id, p.name AS parent_name, c.id AS child_id, c.name AS child_name
FROM parent p
JOIN child c ON p.id = c.parent_id;

parent_id | parent_name | child_id | child_name
----------+-------------+----------+------------
        1 | Parent 1    |        1 | Child 1
        1 | Parent 1    |        2 | Child 2
        2 | Parent 2    |        3 | Child 3

SELECT p.id AS parent_id, p.name AS parent_name, c.id AS child_id, c.name AS child_name
FROM parent p
LEFT JOIN child c ON p.id = c.parent_id;

parent_id | parent_name | child_id | child_name
----------+-------------+----------+------------
        1 | Parent 1    |        1 | Child 1
        1 | Parent 1    |        2 | Child 2
        2 | Parent 2    |        3 | Child 3
        2 | Parent 2    |      NULL | NULL