SFMC SQL, for [Where NOT IN] on multiple columns

Question

I have a simple query where I want to see how many records were not added in DE. But with below query I am getting error "Errors: An expression of non-boolean type specified in a context where a condition is expected"

select 
q.email_address, q.uuid
from usca_restaurant_contact_v3 q
where (q.email_address, q.uuid) not in 
(select p.email_address, p.uuid from usca_restaurant_contact_v3_chkng_pk p)

Any other way to do this? I am trying to see how many records from usca_restaurant_contact_v3 were not added into usca_restaurant_contact_v3_chkng_pk?

Answer 1

Just from a quick glance, I would use a left join instead for this. See below:

select 
q.email_address, q.uuid
from usca_restaurant_contact_v3 q
left join (
    select p.email_address, p.uuid
    from usca_restaurant_contact_v3_chkng_pk p
) b
ON q.email_address = b.email_address AND q.uuid = b.uuid
WHERE b.email_address IS NULL
AND b.uuid IS NULL

By using the above WHERE statement it will only pull records from q that do not exist in p.

Answer 2

The solution provided by Gortonington should work.

Alternatively, please try

SELECT 
q.email_address, q.uuid
FROM usca_restaurant_contact_v3 q
LEFT JOIN usca_restaurant_contact_v3_chkng_pk p
ON q.email_address = p.email_address AND q.uuid = p.uuid
WHERE p.email_address IS NULL
AND p.uuid IS NULL

You may also want to double check:

• if column types are the same between the email_address and uuid columns in the 2 data extensions
• if accidently any leading/trailing spaces were added during upload

To debug, you may also want to add an additional AND statement to your WHERE clause comparing for a concrete record you expect.

PS: Note there are also solutions out there like DESelect that allow you to build and easily test this kind of selections without having to write SQL queries.