Case When statement for an Inner Join

mhoward2 — Tue, 07 Feb 2023 19:14:16 GMT

I have a join on a couple tables that takes FOREVER to run. Way way longer than it should and one of the notes I haven't noticed before states:

NOTE: The execution of this query involves performing one or more Cartesian product joins that can not be optimized.

I'm not sure what that means exactly but a quick google indicates that it probably isn't what I'm trying to accomplish.

PROC SQL;
CREATE TABLE WORK.TABLE3 AS
	SELECT t1.*,
		   t2.NAME,
		   t2.DATE
	FROM WORK.TABLE1 t1 INNER JOIN
		 WORK.TABLE2 t2 ON (CASE WHEN t1.CAT1 NOT IN ('CATA', 'CATB') THEN (t1.ID1 = t2.ID1 AND t1.ID2= t2.ID2 AND t2.ID3 = t1.ID3)
							  WHEN t1.CAT1 IN ('CATA', 'CATB') THEN (t1.ID1 = t2.ID1 AND t1.ID2 = t2.ID2)
						 END);
QUIT;

Is this an acceptable way to join? Is there a better way?

Thanks in advance.

Re: Case When statement for an Inner Join

SASKiwi — Tue, 07 Feb 2023 20:13:30 GMT

CASE in a join is not a good idea. Just do a normal join:

PROC SQL;
CREATE TABLE WORK.TABLE3 AS
   SELECT t1.*,
		  t2.NAME,
		  t2.DATE
   FROM WORK.TABLE1 t1 
   INNER JOIN WORK.TABLE2 t2 
   ON (t1.CAT1 NOT IN ('CATA', 'CATB') and t1.ID1 = t2.ID1 AND t1.ID2= t2.ID2 AND t2.ID3 = t1.ID3)
   or (t1.CAT1 IN ('CATA', 'CATB') and t1.ID1 = t2.ID1 AND t1.ID2 = t2.ID2)
   ;
QUIT;

topic Case When statement for an Inner Join in SAS Programming

Case When statement for an Inner Join

Re: Case When statement for an Inner Join