How to deduce the fact of missing data?

Question

SQL

Is there any way to output a string with NULLs (with anything) when there is no data?
I tried to make a temporary table with one record and make a right join to it, but I did not understand what to write in the condition after ON.
Update: My query always returns one record. In the "summary table" (after all joins) there are thousands of records. If no entry passes the where condition, then that should be returned
instead of no entries.
Is it possible?

Answer 1

It is necessary to lay the correct logic in the application, either processing the number of records, or with an exception.
and on the question - FULL OUTER JOIN

-- выбираем несуществующую запись
SELECT pt.pid, t.id, t.text FROM (SELECT 1 AS pid) pt
  FULL OUTER JOIN (
    SELECT 1 AS pid, tt.*
      FROM tbl tt
      -- условие запроса:
      WHERE tt.id = 10
  ) AS t ON pt.pid = t.pid
;

see example on dbfiddle
also LEFT OUTER JOIN , but put condition on request inside brackets ( ) in subquery, see dbfiddle :

-- выбираем несуществующую запись
SELECT pt.pid, t.id, t.text FROM (SELECT 1 AS pid) pt
  LEFT OUTER JOIN (
    SELECT 1 AS pid, tt.*
      FROM tbl tt
      -- условие запроса:
      WHERE tt.id = 10
  ) AS t ON pt.pid = t.pid
;

You can still UNION subquery with NULL , but it will always be inserted.

SELECT id, text FROM tbl
WHERE id = 10
UNION
SELECT NULL, NULL
;

example, above.

Answer 2

The goal is generally not clear. Well, throw out the exception, or form your own sign.
In the general case, if you have no data, then according to the results, you simply have nothing to return and, accordingly, the result is NULL.
https://docs.microsoft.com/ru-ru/sql/t-sql/languag...