Find parent node?

Question

There are two tables one with node names and another with connection details(child, parent) between the nodes find the node which has no parent i.e, root node. Using SQL query.

Answer 1

Here is a way to do this with a subquery:

SELECT *
FROM nodes
WHERE node_id NOT IN
(SELECT child_id FROM connectionTable)

Answer 2

I'd go for NOT EXISTS rather than NOT IN, as NOT IN can get slow.

SELECT *
    FROM nodes
    WHERE NOT EXISTS (SELECT *
                          FROM connectionTable
                          WHERE connectionTable.child_id = nodes.node_id)