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

by


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

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