PROC SQL subqueries

[mglogan]

Hello!

I am learning PROC SQL in more detail.

For the given example subquery:

proc sql;
        select empid, lastname, firstname, city, state
           from sasuser.staffmaster
           where empid in
              (select empid
                 from sasuser.payrollmaster
                 where month(dateofbirth)=2);
quit ;

I wrote what I thought was the equivalent using Join:

proc sql;
	select 
	 	empid, lastname, firstname, city, state
	from 
		sassuer.staffmaster as a
		inner join sasuser.payrollmaster as b
		where month(dateofbirth)=2 
	on a.empid = b.empid ;
quit;

[1] Is the correct?

[2] if so, what is the benefit to using subqueries?

Thank you!

[Patrick]

@mglogan 

Let's say you have in table sasuser.staffmaster one row with empid=1 and in table sasuser.payrollmaster five rows with empid=1

With your code:

The inner join will return 5 rows, the subquery will return 1 row  ...also investigate what EXISTS can do for you.

[PeterClemmensen · Accepted solution]

1) There are a few things wrong with your code.. EmpId is in both tables, so you have to reference one of them. Also, you have a typo in a libref. 

proc sql;
	select 
	 	a.empid, 
      lastname, 
      firstname, 
      city, 
      state
	from 
		sasuser.staffmaster as a
	inner join sasuser.payrollmaster as b
		on month(dateofbirth)=2 
	and a.empid = b.empid ;
quit;

2)

I think this will help you: When to Use Joins and Subqueries

[VDD]

the difference is control over what element populated the results table.

did b over write a?

do I want the value of a or b?