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?

Explain "alternate of except". PS The left join idiom for calculating what except does is a faq. PS [mre] — philipxy, Jul 02 '19 at 21:05

forpas · Accepted Answer · 2019-07-02T19:37:47.290

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.

score 0 · Answer 2 · answered Jul 02 '19 at 20:10

Seems you don't quite understand what EXCEPT does. It returns the distinct rows from the first resultset that do not match anything in the second resultset. And note that using distinct to generate the second resultset has no useful effect.

To achieve the equivalent result using a join, you need to outer join (as you did) but test for non-existence of rows in the unpreserved table (TempNotRunResults). So something like:

set nocount on;

declare @bill table (account_no tinyint, bill_cycle  date);
insert @bill (account_no, bill_cycle) values 
(1, '20190531'), 
(1, '20190430'), 
(2, '20190215'), 
(1, '20190531'); -- notice the duplicate for <1, May 31>

declare @notrun table (account_no tinyint, bill_cycle date);
insert @notrun (account_no, bill_cycle) values 
(1, '20190430'), 
(1, '20190630'), 
(1, '20190430'); -- notice the duplicate for <1, Apr 30>

select account_no, bill_cycle from @bill 
except 
select account_no, bill_cycle from @notrun
;

select bl.account_no, bl.bill_cycle 
from @bill as bl left join @notrun as nr 
on bl.account_no = nr.account_no 
and bl.bill_cycle = nr.bill_cycle
where nr.account_no is null
order by bl.account_no, bl.bill_cycle ;

Notice the duplicate in the join version. EXCEPT removes duplicates. And since EXCEPT tells us that there is no match in TempNotRunResults, it makes no sense to try to include columns from that table when joining - quite simply there are none and you should not expect anything but NULL. However, you did join on ACCOUNT_NO alone in your join version. That is very different logic so it is difficult to know what exactly you are trying to accomplish. And that takes us back to the request from Han - post sample data and your expected results.

Alternate of except in sql

2 Answers2