If a LEFT JOIN and an INNER JOIN return the exact same rows, which is more efficient to use?
For example,
data one;
mrn='1111';cpt='aaa';output;
mrn='2222';cpt='bbb';output;
mrn='3333';cpt='ccc';output;
mrn='4444';cpt='ddd';output;
run;
data two;
mrn='1111';age=22;output;
mrn='2222';age=44;output;
mrn='3333';age=66;output;
mrn='4444';age=11;output;
run;
proc sql noprint;
create table blah1 as
select
a.mrn,
a.cpt,
b.age
from
one a
inner join two b
on a.mrn=b.mrn;
create table blah2 as
select
a.mrn,
a.cpt,
b.age
from
one a
left join two b
on a.mrn=b.mrn;
quit;
Use the one that makes logical sense. In this case you have a 1 to 1 match. If you always expect this use an inner match, if you can't trust this rule and always want the information from a specific table use the right/left join instead.
Thanks. I always thought INNER JOINS were not as efficient as a LEFT JOIN or a RIGHT JOIN because INNER JOINS check rows common to both tables, whereas LEFT JOINS or RIGHT JOINS just check for rows on either the left-hand or right-hand table.
I'm curious what the exceptions are to this rule, if any?
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.