SQL: Query adjacent nodes in a directed graph

Question

I have a graph with nodes {A, B, C, D, ...} and a table which specifies the directed edges between them.

| node_1 | node_2 |
|-----------------|
|      A | B      |
|      A | C      |
|      B | A      |
|      B | D      |
|      D | A      |

We write A ~ B if there is an edge from A to B. So a row where node_1 = A and node_2 = B implies A ~ B. I distinguish between the following types of relations:

A = B if A ~ B and B ~ A
A > B if A ~ B and not B ~ A
A < B if B ~ A and not A ~ B

How can I retrieve all the nodes adjacent to a given node along with their type of relation? For example, a query for A on the above table should return

| node | type |
|------|------|
|    B | =    | (because A ~ B and B ~ A)
|    C | >    | (because A ~ C and not C ~ A)
|    D | <    | (because D ~ A and not A ~ D)
 

Answer 1

Hmmm . . . you can use conditional logic with aggregation:

select (case when node_1 = 'A' then node_2 else node_1 end) as other_node,
       (case when count(*) = 2 then '='
             when max(node_1) = 'A' then '>'
             else '<'
        end) as type
from nodes n
where 'A' in (node_1, node_2)
group by (case when node_1 = 'A' then node_2 else node_1 end);

Here is a db<>fiddle.

This seems like the simplest and probably the most performant solution.