Alternate of except in sql

Question

I want to use alternate of except. I used left join but its not bringing the required column data.

SELECT ACCOUNT_NO, BILL_CYCLE_DATE, 2 FROM CSS_BILL_Job 
WHERE (BILL_CYCLE_DATE = 20190526 OR  BILL_CYCLE_DATE = 20190525) --33612
EXCEPT
SELECT DISTINCT ACCOUNT_NO, BILL_CYCLE_DATE, 2  FROM TempNotRunResults --33505

But now when I use LEFT JOIN.

SELECT A.ACCOUNT_NO, A.BILL_CYCLE_DATE, B.DATE_BILLED,
B.DATE_PAYMENT_DUE,B.TOTAL_BILL_AMT, B.LPC_AMT, B.BILL_FREQ, B.BILL_CYCLE_TYPE
FROM CSS_BILL_Job A LEFT JOIN TempNotRunResults B
ON A.ACCOUNT_NO  = B.ACCOUNT_NO
WHERE (A.BILL_CYCLE_DATE = 20190526 OR  A.BILL_CYCLE_DATE = 20190525)
AND A.ACCOUNT_NO NOT IN ( SELECT ACCOUNT_NO FROM TempNotRunResults)

I see NULLS for B.DATE_PAYMENT_DUE,B.TOTAL_BILL_AMT, B.LPC_AMT, B.BILL_FREQ, B.BILL_CYCLE_TYPE which is not right. How do I populate columns from table B in my Left join query?

Answer 1

If you don't want nulls from the table TempNotRunResults then use this join:

SELECT A.ACCOUNT_NO, A.BILL_CYCLE_DATE, B.DATE_BILLED,
B.DATE_PAYMENT_DUE,B.TOTAL_BILL_AMT, B.LPC_AMT, B.BILL_FREQ, B.BILL_CYCLE_TYPE
FROM CSS_BILL_Job A LEFT JOIN TempNotRunResults B
ON A.ACCOUNT_NO = B.ACCOUNT_NO 
WHERE 
  (A.BILL_CYCLE_DATE = 20190526 OR  A.BILL_CYCLE_DATE = 20190525)
  AND 
  B.ACCOUNT_NO IS NOT NULL

The condition B.ACCOUNT_NO IS NOT NULL fetches only matching rows, which would be done also by using an INNER join:

SELECT A.ACCOUNT_NO, A.BILL_CYCLE_DATE, B.DATE_BILLED,
B.DATE_PAYMENT_DUE,B.TOTAL_BILL_AMT, B.LPC_AMT, B.BILL_FREQ, B.BILL_CYCLE_TYPE
FROM CSS_BILL_Job A INNER JOIN TempNotRunResults B
ON A.ACCOUNT_NO = B.ACCOUNT_NO
WHERE (A.BILL_CYCLE_DATE = 20190526 OR  A.BILL_CYCLE_DATE = 20190525)

But the title of your question is: "Alternate of except in sql", which is the opposite of what you say that you want to achieve: populate columns from table B in my Left join query, because if you want populated rows from table B then you need the matching rows.