BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SAS_inquisitive
Lapis Lazuli | Level 10

Hello,

 

I was trying to understand the mechanism of multiple left joins in SQL.

I wonder how it works? I assume each left join creates an intermediate table and that combines with subequent table. In my example dat1and dat2 yields an intermediate table and it joins with dat3.

Is the total number of observations (records) in multiple left joins equal to the number of observations in first left join?

 

data dat1;
 input id $ x;
 cards;
 a  1
 b  2
 c  3
 ;
 run;
 
 data dat2;
  input id $ y;
  cards;
  a  1
  ;
 run;
 
 data dat3;
  input id $ z;
  cards;
  b 2
 ;
 run;
 proc sql;
   create table t1 as
    select a.*,b.*,c.*
    from dat1 as a 
        left join dat2 as b
          on a.id = b.id
          left join dat3 as c
          on a.id = c.id;
 quit;
1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

 


I wonder how it works? I assume each left join creates an intermediate table and that combines with subsequent table. In my example dat1and dat2 yields an intermediate table and it joins with dat3.

 

True. Unless you alter join operations order with parentheses.

 

Is the total number of observations (records) in multiple left joins equal to the number of observations in first left join?

 

Not necessarily, if there are multiple matches on the right side, there can be more records in the joined table than there were in the left table.


 

 

 

 

PG

View solution in original post

4 REPLIES 4
LinusH
Tourmaline | Level 20
Yes, for #1 you are correct.
But for any SQL join no of observations in the result set is dependent on the source data, key values and join criteria. Joins creates (at least logically - physical optimization haooends behind the scenes) a product which is filtered by the join criteria.
Data never sleeps
SAS_inquisitive
Lapis Lazuli | Level 10
@ LinusH, since the first left join preserves the number of observations in table a (dat1), this eventually will be preserved in later left joins, right?
PGStats
Opal | Level 21

 


I wonder how it works? I assume each left join creates an intermediate table and that combines with subsequent table. In my example dat1and dat2 yields an intermediate table and it joins with dat3.

 

True. Unless you alter join operations order with parentheses.

 

Is the total number of observations (records) in multiple left joins equal to the number of observations in first left join?

 

Not necessarily, if there are multiple matches on the right side, there can be more records in the joined table than there were in the left table.


 

 

 

 

PG
LinusH
Tourmaline | Level 20
Not necessarily.
As per my previous answer, ech join could be 1 to many, or even many to many which would expand the no of result observations.
Data never sleeps

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 12041 views
  • 3 likes
  • 3 in conversation