Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
290 views
in Technique[技术] by (71.8m points)

sql server - 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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...