Get root, inner, and leaf nodes from a table?

Given a table of nodes (their Ids and their Parent Ids), how can I get all root, inner, and leaf nodes?

Here’s what I have so far:

Root Nodes

SELECT Id, "Root" FROM NodeTable
WHERE ParentId IS NULL;

Inner Nodes

???

Leaf Nodes

SELECT N1.Id, "Leaf" FROM NodeTable N1
LEFT JOIN NodeTable N2 ON N1.Id = N2.ParentId
WHERE N2.ParentId IS NULL;

Is this correct? Is there a way I can do this in just one query?

Answer

Your “Inner Nodes” query can be derived similarly to your “Leaf Nodes” query.

SELECT DISTINCT N1.Id, "Inner"
FROM NodeTable N1
JOIN NodeTable N2
  ON N2.ParentId = N1.ID
WHERE N1.ParentId IS NOT NULL;

This will get you all of the nodes (N1) which aren’t root nodes (because they have a non-null ParentId) and which have children (N2).

And yes, you can sort of do all of this in one query, by using the UNION keyword.

SELECT Id, "Root" 
FROM NodeTable
WHERE ParentId IS NULL
UNION
SELECT DISTINCT N1.Id, "Inner"
FROM NodeTable N1
JOIN NodeTable N2
  ON N2.ParentId = N1.ID
WHERE N1.ParentId IS NOT NULL
UNION
SELECT N1.Id, "Leaf" 
FROM NodeTable N1
LEFT JOIN NodeTable N2 
  ON N1.Id = N2.ParentId
WHERE N2.ParentId IS NULL;