SQL Query EXCEPT not functioning as expected

Question

I have 2 tables within SFMC both with SubscriberKey as the only field, which is the primary Key.

There are SubscriberKeys that exist in both tables.

Test1 has 1,700,000 records and Test 2 has 400,000 records, of which exist in Test1. I am trying to use EXCEPT to pull out all 400k from Test1 and push into a new DE, Test3.

I seem to remember using EXCEPT, which has worked for me before, but cannot seem to get it to work this time.

The query I am using is:

select * from [Test 1]

EXCEPT

select * from [Test 2]

I've tried to keep it as simple as possible, however running the above query gives me the exact amount of records as in the table Test1 (1,700,000), where i would expect (1,300,000).

Answer 1

You could do something like this:

select t1.subscriberkey 
from [test 1] t1
left join [test 2] t2 on (t2.subscriberkey = t1.subscriberkey)
where t2.subscriberkey is null

or

select t1.subscriberkey 
from [test 1] t1
where t1.subscriberkey not in ( 
  select t2.subscriberkey
  from [test 2] t2
)

or

select t1.subscriberkey 
from [test 1] t1
where not exists ( 
  select *
  from [test 2] t2
  where t2.subscriberkey = t1.subscriberkey
)