title: sql use different columns for same query (directed graph as undirected )
body: <p>Suppose I have a table of relationships like in a directed graph. For some pairs of ids there are both 1-&gt;2 and 2-&gt;1 relations, for others there are not. Some nodes are only present in one column.</p>
  <pre><code>a    b
  1    2
  2    1
  1    3
  4    1
  5    2
  </code></pre>
  <p>Now I want to work with it as undirected graph. For example, grouping, filtering using both columns present. For example filter node 5 and count neighbors of the rest</p>
  <pre><code>node neighbor_count
    1    3
    2    1
    3    1
    4    1
  </code></pre>
  <p>Is it possible to compose queries in such a way that first column <code>a</code> is used and then column <code>b</code> is used in the same manner?</p>
  <p>I know it is achievable by doubling the table:</p>
  <pre><code>select a,count(distinct(b)) 
  from 
      (select * from grap 
      union all
      select b as a, a as b from grap) 
  where (not a in (5,6,7)) and (not b in (5,6,7)) 
  group by a;
  </code></pre>
  <p>However, the real tables are quite large (10^9 - 1^10 of pairs). Would <code>union</code> require additional disk usage? A single scan through the base is already quite slow for me. Are there better ways to do this?</p>
  <p>(Currently database is sqlite, but the less platform specific the answer the better)</p>