Return row in SQL that returning null value? to show them not as blank value

Question

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

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?

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?

Side note: ["Bad habits : Putting NOLOCK everywhere"](https://www.sentryone.com/blog/aaronbertrand/bad-habits-nolock-everywhere) — sticky bit, Jul 08 '21 at 23:59
hey sorry to bother you again, is there way to get it to show if I don't know the 3 values above? Let's say given a list, I want to show the null ones. — learning coding, Jul 09 '21 at 00:38
If that comment should address my answer: If you have a list, you know them. 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. — sticky bit, Jul 09 '21 at 01:52

sticky bit · Accepted Answer · 2021-07-09T00:17:37.670

You can left join to a derived table containing the "numbers" (well, strings with a possible numeric representation actually).

SELECT d.clientnumber
       FROM (VALUES ('00602'),
                    ('00897'),
                    ('00940')) AS n
                                  (n)
            LEFT JOIN dataentry AS de
                      ON de.clientnumber = n.n;

That'll give you as many NULLs as "numbers" in the list that aren't present in the table and any "number" that is in the table the amount of times it is in it.

Or, what may be a bit more of a practical use, use NOT EXISTS to get the "numbers" not in the table (yet).

SELECT n.n AS clientnumber
       FROM (VALUES ('00602'),
                    ('00897'),
                    ('00940')) AS n
                                  (n)
       WHERE NOT EXISTS (SELECT *
                                FROM dataentry AS de
                                WHERE de.clientnumber = n.n);

Or even an indicator whether they are present or not.

SELECT n.n AS clientnumber,
       CASE
         WHEN EXISTS (SELECT *
                             FROM dataentry AS de
                             WHERE de.clientnumber = n.n) THEN
           'true'
         ELSE
           'false'
       END existence
       FROM (VALUES ('00602'),
                    ('00897'),
                    ('00940')) AS n
                                  (n);

Return row in SQL that returning null value? to show them not as blank value

1 Answers1

Linked