I have a problem that's really confounding me. I half-expect one of you to point out some really dumb mistake that I'm overlooking but I'm really just not seeing it.
I have a table that our production processes have been feeding for something like a year and we just got some crazy tables from our client against which we are trying to match data. In the following queries, tableA
is my table and tableB
is the table we've just imported.
The basic problem is that
select *
from tableA
where convert(nvarchar(30),accountNum) not in (
select CisAC
from tableB
)
isn't returning any records when I believe it should be. I think that it should find any records in tableA where the accountNum matches the CisAC field in tableB. Right? CisAC is an nvarchar(30) and our accountNum field is a bigint.
To point out why I think an empty return set is wrong:
select * from tableA where convert(nvarchar(30),accountNum) = '336906210032'
returns one record but
select * from tableB where CisAC = '336906210032'
does not.
So, what gives? (And thanks for your time!)
This is probably the classic not in
mistake. If table B contains any null
value,
where convert(nvarchar(30),accountNum) not in (
select CisAC
from tableB
)
will never succeed. You can write it out like:
where convert(nvarchar(30),accountNum) <> null and convert(nvarchar(30),accountNum) <> ...
Since any comparison to null
evaluates to unknown
, this condition is never true.
Replacing the query with a join
like the podiluska's answer suggests should do the trick.