I have two tables, one with orders that looks something like this

Now I want to check if the BINs in the first table are not present in the second table against the coupon.

How do I write a Redshift query for this?

For example, my output from the sample tables would be O2, C2, B3

CodePudding user response：

An anti-join will find the rows of the first table which BIN value is not present in the second one. For example:

select o.*
from o
leff join c on c.bin = o.bin
where c.bin is null 

CodePudding user response：

select o.*
from order o
leff join coupon c on o.bin=c.bin and o.coupon=c.coupon
where c.bin is null