Default row values if returned row is blank

Question

I have a sample query below:

Select column1, column2, column3
from table1
where column1 = 3

and the result is blank

Question: How to update the query that it will return this default result instead of blank rows?

column1	column2	column3
no rows found

I tried a case expression but its still returning a blank row

Answer 1

You have to return a record when your search does not match anything

You can achieve this result using UNION operator, to add to your result set the default row you desire when main search give no results:

declare @src varchar(50) = '3'

Select column1, column2, column3
from table1
where column1 = @src

UNION ALL

Select 'no rows found', null column2, null column3
where not exists (select null from table1 where column1 = @src)

you can write it using cte for readability

declare @src varchar(50) = '3'

;with
src as (
    Select column1, column2, column3
    from table1
    where column1 = @src
)
    select * from src

    UNION ALL

    Select 'no rows found', null column2, null column3
    where not exists (select null from src)

Answer 2

The existing answers require reading from the table multiple times. It is possible to do this with only reading once from the table

SELECT TOP (1) WITH TIES
  column1,
  column2,
  column3
FROM (
    SELECT
      column1,
      column2,
      column3,
      1 AS Ordering
    FROM table1 t1
    WHERE t1.column1 = 3

    UNION ALL

    SELECT
      'no rows found',
      NULL,
      NULL,
      2
) t
ORDER BY Ordering;

Note that there should be no actual sorting involved, as the compiler can see that different unique values come out of each part of the union.