How to combine two tables with vary columns and vary relations in SQL

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.

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.

Table A

EMP_ID        Dept_ID          VISIT_CA_DATE  
001            01               5/2/2011                   
002            02               null                     
004            03               6/8/2011 

Table B

EMP_ID         Dept_ID        LAST_OUT        REASON  
001             01             6/1/2011        sick  
003             02             7/2/2011        vacation

Expecting result like this - all EMP_ID with corresponding visit_date info and absent info.

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  

The only way worked for me is below. Is there better way to achieve this? Thanks!

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

You can do a FULL OUTER JOIN. It's basically a LEFT JOIN and a RIGHT JOIN combined. Something like this:

SELECT  COALESCE(A.Emp_Id,B.Emp_Id) Emp_Id, 
        COALESCE(A.Dept_Id,B.Dept_Id) Dept_Id, 
        A.VISIT_CA_DATE,
        B.LAST_OUT,
        B.REASON
FROM TableA A
FULL OUTER JOIN TableB B
ON A.Emp_Id = B.Emp_Id AND A.Dept_Id= B.Dept_Id

SELECT t.EMP_ID, t.Dept_Id, A.VISIT_CA_DATE, B.LAST_OUT, B.REASON
FROM (
SELECT EMP_ID, Dept_Id FROM A
UNION
SELECT EMP_ID, Dept_Id FROM B
) t
LEFT JOIN A ON t.EMP_ID = A.EMP_ID
LEFT JOIN B ON t.EMP_ID = B.EMP_ID

Altough It probably it looks like about the same as you did.