A Visual Guide to SAS SQL Joins
A join is used to combine the observations (records) from two or more datasets (tables) by using value common to each.
Cross Joins (Cartesian Product)
Cross joins return the number of observations equal to the product of all observations in all datasets being joined. For example, if you join one dataset with 5 observations to another dataset with 10 observations, there will be 50 (10*5) observations in the output dataset.
PROC SQL;
Create Table Employee_Cross as
Select * from Employee CROSS JOIN Employee_Details;
Quit;
Inner Join
Inner joins combine all the observations from two or more datasets based on a common variable (column name) between them and returns only matching observations in two or more datasets.
PROC SQL;
Create Table Employee_Inner as
Select * from Employee as a,Employee_Details as b
where a.EmployeeId=b.EmployeeId;
Quit;
Left Join
A left join returns all the observations from the left dataset and matching observations from the right dataset.
PROC SQL;
Create Table Employee_Left as
Select * from Employee as a Left Join Employee_Details as b
ON a.EmployeeId=b.EmployeeId;
Quit;
Right Join
Right joins return all the observations from the right dataset and matching observations from the left dataset.
PROC SQL;
Create Table Employee_Right as
Select * from Employee as a Right Join Employee_Details as b
ON a.EmployeeId=b.EmployeeId;
Quit;
Full Join
A full join returns all the observations from the left and right datasets.
PROC SQL;
Create Table Employee_Full as
Select * from Employee as a Full Join Employee_Details as b
ON a.EmployeeId=b.EmployeeId;
Quit;
Conclusion
That's it! If you're a veteran, you should know how these joins work in most applicable databases, but now you know how to make them happen using SAS SQL.