如何实现SQL查询node表中无父节点的所有记录？

场景说明 #

现有一张名为node的表，结构与数据如下：

id | name      | child_id  | 
----------------------------
1  | node1     | NULL      | 
2  | node2     | 3         |   
3  | node3     | NULL      |    

各节点关系：

• node1：无父节点、无子节点
• node2：无父节点，子节点为node3
• node3：父节点为node2，无子节点

需要查询所有无父节点的记录，预期结果：

id | name      | child_id  | 
----------------------------
1  | node1     | NULL      | 
2  | node2     | 3         | 

解决方案 #

无父节点的核心判定逻辑是：该节点的id从未出现在其他节点的child_id字段中。以下是两种常用实现方式：

方式一：使用NOT IN子查询

SELECT *
FROM node
WHERE id NOT IN (
    SELECT child_id
    FROM node
    WHERE child_id IS NOT NULL
);

子查询先过滤出所有实际存在的子节点ID（排除child_id为NULL的情况），主查询再筛选出ID不在这个集合里的记录，即为无父节点的节点。

方式二：使用LEFT JOIN关联筛选

SELECT n.*
FROM node n
LEFT JOIN node parent ON n.id = parent.child_id
WHERE parent.id IS NULL;

将表自连接：把node表作为子节点表n，和作为父节点表的node关联（父节点的child_id等于子节点的id）。若节点无父节点，左连接后父节点表的所有字段会是NULL，通过parent.id IS NULL即可筛选出目标记录。

补充说明 #

两种写法都能得到预期结果，在数据量较大的场景下，LEFT JOIN的性能通常更稳定，部分数据库对NOT IN子查询的优化效果不如JOIN语句。

内容的提问来源于stack exchange，提问作者Michael