Inner Join vs subquery

[Babloo]

Following original query yields 5021 records.

proc sql;
Select policy_id as khiref from DEXTCO.d_pol_policy where policy_sk in (Select  policy_sk from  DEXTCO.f_clm_claim);
quit;

whereas If I use SQL join transformation using inner join, I receive 86804 records. Code of my SQL join transformation is

proc sql;
create table work.W6ZV5GFE as
select
D_POL_POLICY.POLICY_ID as KHIREF length = 32 
label = 'Policy id'
from
dextco.F_CLM_CLAIM, 
dextco.D_POL_POLICY
where
(F_CLM_CLAIM.POLICY_SK = D_POL_POLICY.POLICY_SK)
;
quit;

Could you please help me understand to incorporate subquery in SQL join transformation?

[Kurt_Bremser · Accepted solution]

The inner join creates a cartesian product, the subquery only sets the where condition to true and does not multiply observations.

So with the inner join you get an observation for every claim to a policy, while with the subqery you get only one observation per policy if at least one claim exists.

[Babloo]

Could you please help me understand to incorporate subquery in SQL join transformation in DI studio? I looked at this document 

but it was not helpful.

http://support.sas.com/documentation/cdl/en/etlug/66819/HTML/default/viewer.htm#p0tpbt07h7iy7qn1071l...

[LinusH]

Guide someone with a GUI via test messages is hard.

If you can't fulfill your task based on the documentation, you need the means of interactive knowledge sharing.

Have you as a start taken the DI Studio training?

A part from that, I guess some sort of on site support from some consultant is the most appropriate way forward.

DI Studio is a great tool, if you know how to use it. Sounds like you are alone, and I think that a single user environment is not the best use case for DIS. The overhead by defining metadata, and understanding and structuring this abstraction layer will not repay if the solution is small.

[Kurt_Bremser]

---

@Babloo wrote:

Could you please help me understand to incorporate subquery in SQL join transformation in DI studio? I looked at this document 

but it was not helpful.

http://support.sas.com/documentation/cdl/en/etlug/66819/HTML/default/viewer.htm#p0tpbt07h7iy7qn1071l...

---

I could not think of anything anybody here could tell you in excess of that formidable documentation page. If that does not work for you, you need some serious onsite training with someone who can literally guide your hand.

And knowing from experience how important working from SAS documentation is, I ask you to question yourself if working with SAS is a field of work you are suited for.