ISNULL or any alternative in sql server when not data present for subquery

Question

I have a situation, Where I need to get some data in case I find no data for certain query. e.g.

select id from abc where userid='XYZ'

In this case I will get the Id, only if the record with userid='XYZ' is present in the table abc.

Below given code will give 123 only if id is present as null .This is not what I'm expecting.

select isnull(id,123) from abc where userid='XYZ'

I want something like:
If no data in abc with userid='XYZ', then some specific value should be output.
e.g.

select isnull((select id from abc where userid='XYZ'),123)

Is there any similar shortcut for the above query?? Please suggest. Thanks in advance

Answer 1

isnull work on the "column" level, what you need is if a row is null, then print something else.

I would UNION ALL your select with a "dummy" select containing only one row with the data you want to be displayed in case nothing returns, then select top 1 from that with a order. Like

select top 1 id
from (
    select id, 1 from abc where userid='XYZ'
    UNION ALL
    select 123, 0
) X
order by 1 DESC