/**********************************************************************
 * STAT 5110/6110 - SAS Programming and Applications
 * Peng Zeng - Auburn University
 * 4-20-2022
 **********************************************************************/

data one;
   input x a $;
datalines;
1 a
4 d
2 b
;
data two;
   input x b $;
datalines;
2 x
3 y 
5 v
;

data three;
   input x a $;
datalines;
1 a1
1 a2
2 b1 
2 b2
4 d
;

data four;
   input x b $;
datalines;
2 x1
2 x2
3 y
5 v
;
run; 

/**********************************************************************
 * cartesian product
 **********************************************************************/

proc sql;
   select * from one, two; 
quit; 

/**********************************************************************
 * inner joins
 **********************************************************************/

proc sql; 
   select * 
   from one, two
   where one.x = two.x;
quit; 

/**********************************************************************
 * inner joins - select columns
 **********************************************************************/

proc sql; 
   select one.x, a, b 
   from one, two
   where one.x = two.x;
quit; 

/**********************************************************************
 * inner joins - nonunique matching 
 **********************************************************************/

proc sql; 
   select * 
   from three, four
   where three.x = four.x;
quit; 

/**********************************************************************
 * left join 
 **********************************************************************/

proc sql; 
   select *
   from one left join two
   on one.x = two.x;
quit; 

/**********************************************************************
 * right join 
 **********************************************************************/

proc sql; 
   select *
   from one right join two
   on one.x = two.x;
quit; 

/**********************************************************************
 * full join 
 **********************************************************************/

proc sql; 
   select *
   from one full join two
   on one.x = two.x;
quit; 

/**********************************************************************
 * use table alias 
 **********************************************************************/

proc sql; 
   select *
   from one as a right join two as b
   on a.x = b.x;
quit;

/**********************************************************************
 * THE END
 **********************************************************************/