Q:

how to use same column names with different data types in subquery in sqlserver

I have a query:
select * 
from proformaentry 
where cast(tcmno as varchar) not in(
    select tcmno from contreceipt
)

I want to list all tcmno from proformaentry table which is not in the table contreceipt. In proformaentry table, tcmno is int datatype but in contreceipt, it is varchar type. So I used above query, but it is not returning any rows.
Actually there are some tcmno in proformaentry table which is not present in contreceipt.
Hope I can get the correct query to retrieve the rows from here.

A:

I'm guessing contrecipt.tcmo is NULLABLE. NOT IN clause is the same as nested AND <> clauses. Example:
declare @tbl table(n int)
insert into @tbl values (1), (2), (3)

select * from @tbl where not n in(4, 5, 6, null)

The above query will not return any rows. If you transform the NOT IN, it'll be:
select * from @tbl where n <> 4 and n <> 5 and n <> 6 and n <> null

And since n <> null is FALSE, for the purpose of a WHERE clause, the NOT IN resulted to FALSE. In order to achieve the desired result, you can use NOT EXISTS:
select * 
from proformaentry p 
where not exists(
    select 1 from contreceipt where tcmno = cast(p.tcmno as varchar(20))
)

