How to do inner join on two tables in Teradata

Scenario: How to 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? 

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:

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  

Solution:

SELECT A.EMP_ID,A.Dept_ID,A.VISIT_CA_DATE,B.LAST_OUT,B.REASON
from TABLE_A A 
INNER JOIN TABLE_B B ON A.EMP_ID = B.EMP_ID AND A.DEPT_ID = B.DEPT_ID;

nVector

posted on 01 Aug 18