topic Condition on Inner Join and the rest. in SAS Enterprise Guide

Condition on Inner Join and the rest.

ralizadeh — Tue, 16 May 2023 23:13:52 GMT

Could someone clarify the effect of using different conditions, such as "greater," "smaller," or "not equal," when joining tables? I initially thought that an inner join requires an equal condition, but now I see that other conditions are possible. How does selecting "not equal" instead of "equal" impact the inner join?

PROC SQL;
   CREATE TABLE WORK.Non_Preg_Count AS 
   SELECT t1.CIN AS CIN1, 
          t1.New_Month AS New_Month1, 
          t2.CIN AS CIN2, 
          t2.New_Month AS New_Month2
      FROM WORK.MERGED_FINAL t1
           INNER JOIN WORK.MERGED_PREG_FINAL t2 ON (t1.CIN ^= t2.CIN);
QUIT;

Re: Condition on Inner Join and the rest.

Tom — Wed, 17 May 2023 00:50:43 GMT

The INNER JOIN means that both sides have to contribute to the join by matching based on the ON criteria.

The use of NOT EQUAL operator as part of the logic of the ON condition just means that the test that needs to succeed for the combination to be considered a match is that the two values being compared are not equal.

Re: Condition on Inner Join and the rest.

ralizadeh — Wed, 17 May 2023 01:55:08 GMT

Thanks @Tom , but I am not sure if I get your point. In the picture I shared, the ON condition is based on CIN i.e. t1.CIN1 = t2.CIN2. So, it won't make sense to me to have t1.CIN1=t2.CIN2 in my INNER JOIN and also t1.CIN1 ^= t2.CIN2 as another condition. Am I missing something here?

Re: Condition on Inner Join and the rest.

TomKari — Wed, 17 May 2023 02:42:35 GMT

It's a good question, but I think you've got yourself into a bit of a muddle. I explain a SQL join as "First, all of the records are matched with all of the records (a cartesian join)." "Second, based on your selection criteria, any records that don't match the criteria are removed". So, suppose t1.CIN values are a, c, e and t2.CIN values are b, c, e. After step one, you'll have 9 records with keys a, b; a, c; a, e; c, b; c, c; c, e; e, b; e, c; e, e. Now, step 2, follow the selection criteria. This will remove records c, c and e, e leaving you with 7 records.

Tom

Re: Condition on Inner Join and the rest.

mkeintz — Wed, 17 May 2023 02:55:41 GMT

@ralizadeh wrote (bold italics mine)

Thanks @Tom , but I am not sure if I get your point. In the picture I shared, the ON condition is based on CIN i.e. t1.CIN1 = t2.CIN2. So, it won't make sense to me to have t1.CIN1=t2.CIN2 in my INNER JOIN and also t1.CIN1 ^= t2.CIN2 as another condition. Am I missing something here?

I don't see any instance of t1.cin=t2.cin in your picture (reproduced below) or in the associated code box. BTW, you typed t1.cin1=t2.cin2 - I presume you meant t1.cin=t2.cin.

Re: Condition on Inner Join and the rest.

TomKari — Wed, 17 May 2023 03:08:37 GMT

SQL can be a little weird, especially when you're using something like EG, which tries to give you a user-friendly interface. It might be easier for you to do some experimenting, and see what happens. I suggest you try these as t1 and t2, and see what happens. Feel free to post questions. It'll give everybody a basis for discussion.

T!:

Recnum1	CIN
101	a
102	(null)
103	a
104	c
105	e
106	(null)
107	f
108	f
109	g

T2:

Recnum2	CIN
201	(null)
202	a
203	a
204	b
205	b
206	c
207	c
208	(null)
209	g

Re: Condition on Inner Join and the rest.

Tom — Wed, 17 May 2023 03:17:01 GMT

@ralizadeh wrote:

Thanks @Tom , but I am not sure if I get your point. In the picture I shared, the ON condition is based on CIN i.e. t1.CIN1 = t2.CIN2. So, it won't make sense to me to have t1.CIN1=t2.CIN2 in my INNER JOIN and also t1.CIN1 ^= t2.CIN2 as another condition. Am I missing something here?

I don't understand what you are asking. That GUI tool looks like it is used to generate the SQL by letting your point and click. So if you select the = operator it generates code with the = operator. If you select the ^= operator it generates code with the ^= operator. Note that the ^ is just short hand for NOT. In SAS code you can also just the pneumonic NE for not equal in the code.

Re: Condition on Inner Join and the rest.

ralizadeh — Wed, 17 May 2023 04:01:30 GMT

Thanks @TomKari I am getting somewhere now. So, in this case, if I want to pull the CINs that are NOT in t2 table how should I proceed? should I just use Inner Join with t1.CIN ^= t2.CIN?

I am trying to get this with SAS EG:

ELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.name = TableB.name
WHERE TableB.id IS null

Re: Condition on Inner Join and the rest.

SASKiwi — Wed, 17 May 2023 04:23:58 GMT

Try this:

proc sql;
  create table Want AS 
  select T1.*
  from WORK.MERGED_FINAL t1
  left join WORK.MERGED_PREG_FINAL t2 
  on t1.CIN = t2.CIN
  where missing(t2.CIN)
  ;
quit;