title: Full Outer Self Join
body: <p>The problem is to return the rows which contain nulls as well. Below is SQL code to create table and populate it with sample data.</p>
  
  <p>I'm expecting below, but query does not show the two rows with null values.</p>
  
  <pre>
  src_t1  id1_t1  id2_t1  val_t1     src_t2  id1_t2  id2_t2  val_t2
  
                                          b       z      z        4
  a        w       w      100             b       w      w        1
  a        x       x      200             b       x      x        2
  a        y       y      300
  
  </pre>
  
  <p>Data:</p>
  
  <pre><code>CREATE TABLE sample (
      src VARCHAR(6)
      ,id1 VARCHAR(6)
      ,id2 VARCHAR(6)
      ,val FLOAT
  );
  
  INSERT INTO sample (src, id1, id2, val)
  VALUES ('a', 'w', 'w', 100)
        ,('b', 'w', 'w', 1) 
        ,('a', 'x', 'x', 200)
        ,('b', 'x', 'x', 2) 
        ,('a', 'y', 'y', 300)
        ,('b', 'z', 'z', 4) 
  ;
  </code></pre>
  
  <p>This is my test query. It does not show results when t1.src = 'a' and t1.id1 = 'y' or when t2.src = 'b' and t2.id1 = 'z'. </p>
  
  <p>Why? </p>
  
  <p>What's the correct query?</p>
  
  <pre><code>SELECT t1.src, t1.id1, t1.id2, t1.val
    ,t2.src as src2, t2.id1, t2.id2, t2.val
  FROM sample t1 FULL OUTER JOIN sample t2
    ON t1.id1 = t2.id1 AND t1.id2 = t2.id2
  WHERE (t1.src = 'a' AND t2.src = 'b') 
    OR (t1.src IS NULL AND t1.id1 IS NULL AND t1.id2 IS NULL)
    OR (t2.src IS NULL AND t2.id1 IS NULL AND t2.id2 IS NULL)
  </code></pre>
  
  <p>I've also tried moving the conditions in the <code>WHERE</code> clause to the <code>ON</code> clause as well.</p>
  
  <p>TIA.</p>