Field not equal to any value from a different column-CodePudding

Table1 has only one column (customer_id) and contains a list of customer ids I want to exclude from my analysis.
OverallSalesTable contains customer_id too (all the ones I want to exclude others I want to include) and other attributes (I kept only sale_amount here)

I'm doing this :

Select a1.customer_id, a1.sales_amount
from OverallSalesTable a1
left join Table1 a2 on a1.customer_id = a2.customer_id
where a1.customer_id not in a2.customer_id

But the last line causes an error.
If I do where a1.customer_id <> a2.customer_id instead, no error but it returns a blank table (no values). I am 100% sure that OverallSalesTable contains some customer_id that are not in Table1.

Is there a nice way to do this?

CodePudding user response：

Just use NOT EXISTS. NOT EXISTS will be true or false. While using a WHERE a = b, you will remove all items, which have the same value.

Try this:

SELECT a1.customer_id, a1.sales_amount
FROM OverallSalesTable a1 left join Table1 a2 
ON a1.customer_id = a2.customer_id
WHERE NOT EXISTS 
(SELECT a2.customer_id FROM Table1 WHERE a1.customer_id = a2.customer_id)