Marketing Cloud Query Activity

Question

I have tried to run the below query in marketing cloud and it has thrown below error. "error saving the query field.an expression of non-boolean type specified in a context where a condition is expected, near ','."

Query is-

Select orderid,
       orddate 
from table1 
where (orderid,orddate) 
not in (
       select orderid, 
              orddate 
       from table2
)

Can someone help me to resolve the issue.

Thanks in Advance Nikhila

Answer 1

For T-SQL and SFMC SQL, you cannot do a NOT IN with multiple columns in it, which means your above is invalid.

BUT, you can do a not exists instead and it actually should be more performant than your not in.

Select t1.orderid,
       t1.orddate 
from table1 t1
where NOT EXISTS (
       select TOP 1 t2.orderid, 
                    t2.orddate 
       from table2 t2
       where t2.orderid = t1.orderid
       and t2.orddate = t1.orddate
)

This will grab any record from table 1 where they do not match inside of table 2 - as the not exist will only grab a record if the orderid and orddate match the record in table 1, which will then exclude that record.