Left Join missing rows from left table

[sasuser_sk]

Hi: I have 280K records in left table and over a million in table 2. My join brings me 100K records only. I don't understand the problem as to why data is dropped in left join. I have tried it two ways but both bring back 100K records. Help will be greatly appreciated.

Methods1:
PROC SQL;
CREATE TABLE WORK.ASSIGNS AS
SELECT DISTINCT t1.ACCT_NO,
t1.ACCT_NO_DET,
t1.PROD_ONE,
t1.PROD_TWO,
t1.START_DATE,
t1.END_DATE,
t2.PROD_DETAILS

FROM WORK.CUSTOMER t1
LEFT JOIN SCHEMA.PRODUCTS t2 ON (t1.ACCT = t2.ACCT_NO) AND (t1.ACCT_NO_DET = t2.ACCT_NO_DET)
WHERE t2.PROD_DETAILS LIKE 'ACCESSORY' ;
QUIT;

Method 2:

PROC SQL;
CREATE TABLE WORK.ASSIGNS AS
SELECT DISTINCT t1.ACCT_NO,
t1.ACCT_NO_DET,
t1.PROD_ONE,
t1.PROD_TWO,
t1.START_DATE,
t1.END_DATE,
t2.PROD_DETAILS

FROM WORK.CUSTOMER t1
LEFT JOIN SCHEMA.PRODUCTS t2 ON (t1.ACCT = t2.ACCT_NO) AND (t1.ACCT_NO_DET = t2.ACCT_NO_DET)
WHERE t2.PROD_DETAILS LIKE 'ACCESSORY' or t2.PROD_DETAILS is  missing ;
QUIT;

[Reeza]

Your WHERE clause is filtering out the records most likely.

[sasuser_sk]

I tried AND too instead of where and that bring me 320K records back.

[PGStats · Accepted solution]

You must force the where clause to be evaluated before the join. Try this:

PROC SQL;
CREATE TABLE WORK.ASSIGNS AS
SELECT DISTINCT t1.ACCT_NO,
	t1.ACCT_NO_DET,
	t1.PROD_ONE,
	t1.PROD_TWO,
	t1.START_DATE,
	t1.END_DATE,
	t2.PROD_DETAILS
FROM 
	WORK.CUSTOMER as t1 LEFT JOIN 
	(	select * 
		from  SCHEMA.PRODUCTS
		WHERE PROD_DETAILS LIKE 'ACCESSORY'  ) as t2 
		ON t1.ACCT = t2.ACCT_NO AND t1.ACCT_NO_DET = t2.ACCT_NO_DET;
QUIT;

[sasuser_sk]

Thank you! It worked.