CODEWITHSUNDEEP
sqlsql-servert-sql
Ethan Pelton
Ethan Pelton

Reputation: 1796

Trouble with tsql not in query

I'm trying to find rows where a value in table 1 column A are not in table 2 column A

This is the query...

SELECT contactsid
FROM contacts
WHERE (email1 NOT IN (SELECT email
                      FROM old_contact))

It returns 0 rows, which I know is incorrect. There are many rows in contacts.email1 that are not in old_contact.email

How should I be writing this query?

Upvotes: 2

Views: 65

Answers (2)

rory.ap
rory.ap

Reputation: 35270

Try:

SELECT contactsid
FROM Contacts a
LEFT JOIN old_contact b
ON a.email1 = b.email
WHERE b.email IS NULL

This will join Contacts to old_contact using a LEFT JOIN -- a type of join that, based on the join condition, returns all records from the left side (i.e. Contacts) even if no records exist on the right side. Then, the WHERE clause filters the results so that it returns only the records from the left side where the ride side records don't exist.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269763

My guess is that old_contract.email takes on a NULL value.

For this reason, not exists is often a better choice:

SELECT contactsid
FROM contacts c
WHERE NOT EXISTS (SELECT 1
                  FROM old_contract oc
                  WHERE c.email = oc.email1
                 ) ;

You could also add where email1 is not null to the subquery. However, I find just using not exists is generally safer in case I forget that condition.

Upvotes: 7

Related Questions