Как определить в SQL, что узел является корневым, листом или внутренним узлом

Что проверяют

Умеете ли вы работать с деревом в модели списка смежности (id, parent_id) и не попадаете ли в ловушки с NULL. Следом обычно просят вывести поддерево или путь до корня, то есть написать рекурсивный запрос.

Как отвечать

Сначала определения: у корня нет родителя, у листа нет детей, у внутреннего узла есть и то и другое. Корень без детей одновременно и корень, и лист, это стоит оговорить вслух.

Всё решается одним запросом с CASE и EXISTS:

SELECT n.id,
  CASE
    WHEN n.parent_id IS NULL THEN 'root'
    WHEN EXISTS (SELECT 1 FROM nodes c WHERE c.parent_id = n.id) THEN 'inner'
    ELSE 'leaf'
  END AS kind
FROM nodes n;

Порядок веток важен: корень проверяется первым, иначе корень с детьми окажется «внутренним». Для скорости нужен индекс по parent_id, тогда EXISTS становится коротким поиском по индексу, а не сканом таблицы на каждую строку. PostgreSQL сам такой индекс не создаёт, даже если на колонке висит внешний ключ.

Для обхода используйте WITH RECURSIVE: стартовая часть берёт корень, рекурсивная присоединяет детей по parent_id, глубину считают полем depth + 1. От циклов в кривых данных защищаются ограничением глубины или, начиная с PostgreSQL 14, конструкцией CYCLE.

В Go читайте parent_id в sql.NullInt64 или в указатель *int64: обычный int64 на NULL вернёт ошибку при Scan. В pgx работают те же указатели или pgtype.Int8.

Где спотыкаются

Ищут листья через NOT IN (SELECT parent_id FROM nodes). Подзапрос содержит NULL от корня, а NOT IN по списку с NULL не возвращает ни одной строки. Нужен NOT EXISTS или фильтр parent_id IS NOT NULL в подзапросе.

Определяют внутренний узел как «чей-то родитель» и забывают исключить корень, у которого тоже есть дети.

Предлагают хранить тип узла в отдельной колонке. Её придётся пересчитывать при каждой вставке и удалении, и рано или поздно она разойдётся с реальной структурой. Если чтение дерева действительно горячее, лучше обсудить materialized path, nested sets или расширение ltree.