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

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

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?

Solution

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);