How to return default value from SQL query

Question

Is there any easy way to return single scalar or default value if query doesn't return any row?

At this moment I have something like this code example:

IF (EXISTS (SELECT * FROM Users WHERE Id = @UserId))       SELECT Name FROM Users WHERE Id = @UserId   ELSE     --default value     SELECT 'John Doe' 

How to do that in better way without using IF-ELSE?

Answer 1

Assuming the name is not nullable and that Id is unique so can match at most one row.

 SELECT      ISNULL(MAX(Name),'John Doe')  FROM      Users   WHERE      Id = @UserId   

Answer 2

Try ISNULL or COALESCE:

SELECT ISNULL((SELECT TOP 1 Name FROM Users WHERE Id = @UserId), 'John Doe') 

The inner select will return nothing if no user exist with this id, the isnull will solve this case.