DZone
Database Zone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
  • Refcardz
  • Trend Reports
  • Webinars
  • Zones
  • |
    • Agile
    • AI
    • Big Data
    • Cloud
    • Database
    • DevOps
    • Integration
    • IoT
    • Java
    • Microservices
    • Open Source
    • Performance
    • Security
    • Web Dev
DZone > Database Zone > A Visual Guide to SAS SQL Joins

A Visual Guide to SAS SQL Joins

If you want to use SAS SQL for your queries, there are some subtle syntax differences that you might not be ready for. Here's how to use some core joins.

Jitendra Bafna user avatar by
Jitendra Bafna
CORE ·
Feb. 23, 17 · Database Zone · Tutorial
Like (1)
Save
Tweet
15.90K Views

Join the DZone community and get the full member experience.

Join For Free

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;


Image title

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;

Image title

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;

Image title

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;

Image title

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;

Image title

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.

Joins (concurrency library) SAS (software) Database sql

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • What Software Developers Can Learn From Andy Warhol
  • Event-Driven Hello World Program
  • Dynamically Provisioning Persistent Volumes with Kubernetes
  • Five Tips to Fasten Your Skewed Joins in Apache Spark

Comments

Database Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • MVB Program
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends:

DZone.com is powered by 

AnswerHub logo