-2

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'
)
Dale K
  • 25,246
  • 15
  • 42
  • 71
learning coding
  • 3
  • 4
  • 3
    Did you read the comment on your previous question about **NOT** using `nolock` willy nilly? – Dale K Jul 09 '21 at 01:35
  • 2
    Please provide a [mre] i.e. sample data, example code with parameters and desired results - because it is not clear at all to me what you are wanting. – Dale K Jul 09 '21 at 01:37
  • 1
    And how actually does this differ from your previous question? – Dale K Jul 09 '21 at 01:41
  • 3
    I did reply to your comment on [your previous question](https://stackoverflow.com/questions/68309782/return-row-in-sql-that-returning-null-value-to-show-them-not-as-blank-value?noredirect=1#comment120728185_68309782): "If you don't know them, you cannot show them of course. I'm not really getting what you're trying to say I'm afraid.". So, you should really elaborate on what you mean here, with a comprehensive [example]. (and yeah, also about the `NOLOCK` thing as @DaleK reminded you again of...) – sticky bit Jul 09 '21 at 02:09
  • 2
    Please read [this](https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) for some tips on improving your question. – HABO Jul 09 '21 at 02:35

1 Answers1

0

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
GGG
  • 486
  • 4
  • 9
  • `right join` is just a `left join` the other way round and confuses you when there are further `inner join`s, which is why nobody uses it – Charlieface Jul 09 '21 at 11:29