# 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?

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;
```