title: Using GROUP BY and HAVING COUNT(*) >1 to select duplicate and non-duplicate field
body: <p>Below is the data in <code>CUST_REF</code> table</p>
  
  <p><code>CUST ID   REF ID
      1        7
      2        2
      3        5
      4        5
      5        5</code></p>
  
  <p>Below query will return <code>3   5</code> and <code>4   5</code> </p>
  
  <pre><code>SELECT CUST_ID, REF_ID
  FROM CUST_REF
  WHERE REF_ID IN 
  (select ref_id from CUST_REF
   group by ref_id
   having (count(*) &gt; 1))
  AND CUST_ID != REF_ID;
  </code></pre>
  
  <p>How about if want to return <code>1   7</code> <code>2   2</code> <code>5   5</code>? I make below query it only able to return <code>1   7</code> and <code>2   2</code></p>
  
  <pre><code>SELECT CUST_ID, REF_ID
  FROM CUST_REF
  WHERE CUST_ID = REF_ID
  AND REF_ID NOT IN 
  (select ref_id from CUST_REF
  group by ref_id
  having (count(*) &gt; 1))
  UNION
  SELECT CUST_ID, REF_ID
  FROM CUST_REF
  WHERE CUST_ID != REF_ID
  AND REF_ID NOT IN 
  (select ref_id from CUST_REF
  group by ref_id
  having (count(*) &gt; 1));
  </code></pre>