title: How to combine two tables with vary columns and vary relations in SQL
body: <p>How do I combine columns and rows in the following example in SQL without pulling extra rows with nulls on columns that not existing in the other table? This should be similar to the OUTER UNION CORR in SAS. </p>
  
  <p>EMP_ID and Dept_ID in the two tables don't have the exact ID list, e.g. ID in table A might not be in table B so as the other way around.</p>
  
  <p>Table A              </p>
  
  <pre><code>EMP_ID        Dept_ID          VISIT_CA_DATE  
  001            01               5/2/2011                   
  002            02               null                     
  004            03               6/8/2011 
  </code></pre>
  
  <p>Table B  </p>
  
  <pre><code>EMP_ID         Dept_ID        LAST_OUT        REASON  
  001             01             6/1/2011        sick  
  003             02             7/2/2011        vacation
  </code></pre>
  
  <p>Expecting result like this - all EMP_ID with corresponding visit_date info and absent info.</p>
  
  <pre><code>EMP_ID     Dept_ID   VISIT_CA_DATE     LAST_OUT       REASON      
  001         01         5/2/2011        6/1/2011        sick    
  002         02         null            null            null    
  003         02         null            7/2/2011        vacation    
  004         03         6/8/2011        null            null  
  </code></pre>
  
  <p>The only way worked for me is below. Is there better way to achieve this? Thanks!</p>
  
  <pre><code>A LEFT OUTER JOIN B on A.EMP_ID=B.EMP_ID 
  UNION 
  B LEFT OUTER JOIN A ON B.EMP_ID=A.EMP_ID
  </code></pre>