sql - Show null values from a set of rows if I DON'T know the value?

Question

So background is clientnumber is value that client sent to me and will be inserted into the database. But so the 3 numbers above aren't inserted in the database by me YET, so it will turn blank when I search them in database. Is there way for SQL to show those numbers as null value?

If I don't know the 3 above values, how can I show them (since they are null value when searched) from the value that does exist?

Maybe one to do this is using not IN with client numbers that I have already inserted? But let's say there are 2000 numbers I inserted, it would be very inefficient. What would be the best way to do this?

Let's say the below are the values that I know, but two of them are null, how do I only show the null values?

select *
from dataentry with (nolock)
where clientnumber in (
'00602',
'00897',
'00940',
'22234',
'87669'
)

标签： sqlsql-servertsqlnullnotin

Answer 1

I think you can use right join

--Let your table only contain these three number 00602,00897,00940
Create Table #temp
(
   clientnumber varchar(10)
)
INSERT INTO #temp
values
('00602'),
('00897'),
('00940')

--Here is your values that you want to test
Declare @table table
(
   clientnumber varchar(10)
)
INSERT INTO @table
values
('00602'),
('00897'),
('00940'),
('22234'),
('87669')

--the following shows the values that does not exists on your table
select ta.clientnumber 
from #temp as tm
right join @table as ta
on tm.clientnumber =ta.clientnumber
where tm.clientnumber is null

--the following shows all values and there are two null values due to no any match on your table
select tm.clientnumber 
from #temp as tm
right join @table as ta
on tm.clientnumber =ta.clientnumber


DROP TABLE #temp