[tsql] Return a value if no rows are found in Microsoft tSQL

Using a Microsoft version of SQL, here's my simple query. If I query a record that doesn't exist then I will get nothing returned. I'd prefer that false (0) is returned in that scenario. Looking for the simplest method to account for no records.

SELECT  CASE
            WHEN S.Id IS NOT NULL AND S.Status = 1 AND (S.WebUserId = @WebUserId OR S.AllowUploads = 1) THEN 1
            ELSE 0
        END AS [Value]

        FROM Sites S

        WHERE S.Id = @SiteId

I read all the answers here, and it took a while to figure out what was going on. The following is based on the answer by Moe Sisko and some related research

If your SQL query does not return any data there is not a field with a null value so neither ISNULL nor COALESCE will work as you want them to. By using a sub query, the top level query gets a field with a null value, and both ISNULL and COALESCE will work as you want/expect them to.

My query

select isnull(
 (select ASSIGNMENTM1.NAME
 from dbo.ASSIGNMENTM1
 where ASSIGNMENTM1.NAME = ?)
, 'Nothing Found') as 'ASSIGNMENTM1.NAME'

My query with comments

select isnull(
--sub query either returns a value or returns nothing (no value)
 (select ASSIGNMENTM1.NAME
 from dbo.ASSIGNMENTM1
 where ASSIGNMENTM1.NAME = ?)
 --If there is a value it is displayed 
 --If no value, it is perceived as a field with a null value, 
 --so the isnull function can give the desired results
, 'Nothing Found') as 'ASSIGNMENTM1.NAME'

@hai-phan's answer using LEFT JOIN is the key, but it might be a bit hard to follow. I had a complicated query that may also return nothing. I just simplified his answer to my need. It's easy to apply to query with many columns.

;WITH CTE AS (
  -- SELECT S.Id, ...
  -- FROM Sites S WHERE Id = @SiteId
  -- EXCEPT SOME CONDITION.
  -- Whatever your query is
)
SELECT CTE.* -- If you want something else instead of NULL, use COALESCE.
FROM (SELECT @SiteId AS ID) R
LEFT JOIN CTE ON CTE.Id = R.ID

Update: This answer on SqlServerCentral is the best. It utilizes this feature of MAX - "MAX returns NULL when there is no row to select."

SELECT ISNULL(MAX(value), 0) FROM table WHERE Id = @SiteId

Something like:

if exists (select top 1 * from Sites S where S.Id IS NOT NULL AND S.Status = 1 AND (S.WebUserId = @WebUserId OR S.AllowUploads = 1))
    select 1
else
    select 0

What about WITH TIES?

SELECT TOP 1 WITH TIES tbl1.* FROM 
        (SELECT CASE WHEN S.Id IS NOT NULL AND S.Status = 1 
                      AND (S.WebUserId = @WebUserId OR 
                           S.AllowUploads = 1)
                     THEN 1 
                     ELSE 0 AS [Value]
         FROM Sites S
         WHERE S.Id = @SiteId) as tbl1
ORDER BY tbl1.[Value]

You only have to replace the WHERE with a LEFT JOIN:

SELECT  CASE
        WHEN S.Id IS NOT NULL AND S.Status = 1 AND ...) THEN 1
        ELSE 0
    END AS [Value]

    FROM (SELECT @SiteId AS Id) R
    LEFT JOIN Sites S ON S.Id = R.Id

This solution allows you to return default values for each column also, for example:

SELECT
    CASE WHEN S.Id IS NULL THEN 0 ELSE S.Col1 END AS Col1,
    S.Col2,
    ISNULL(S.Col3, 0) AS Col3
FROM
    (SELECT @Id AS Id) R
    LEFT JOIN Sites S ON S.Id = R.Id AND S.Status = 1 AND ...

This Might be one way.

SELECT TOP 1 [Column Name] FROM (SELECT [Column Name] FROM [table]
    WHERE [conditions]
    UNION ALL
    SELECT 0 )A ORDER BY [Column Name] DESC

This is similar to Adam Robinson's, but uses ISNULL instead of COUNT.

SELECT ISNULL(
(SELECT 1 FROM Sites S
WHERE S.Id = @SiteId and S.Status = 1 AND 
      (S.WebUserId = @WebUserId OR S.AllowUploads = 1)), 0)

If the inner query has a matching row, then 1 is returned. The outer query (with ISNULL) then returns this value of 1. If the inner query has no matching row, then it doesn't return anything. The outer query treats this like a NULL, and so the ISNULL ends up returning 0.

My solition is working

can testing by change where 1=2 to where 1=1

select * from (
    select col_x,case when count(1) over (partition by 1) =1 then 1 else HIDE end as HIDE from (
    select 'test' col_x,1 as HIDE
    where 1=2
    union 
    select 'if no rows write here that you want' as col_x,0 as HIDE
    ) a
    ) b where HIDE=1

No record matched means no record returned. There's no place for the "value" of 0 to go if no records are found. You could create a crazy UNION query to do what you want but much, much, much better simply to check the number of records in the result set.

This might be a dead horse, another way to return 1 row when no rows exist is to UNION another query and display results when non exist in the table.

SELECT S.Status, COUNT(s.id) AS StatusCount
FROM Sites S
WHERE S.Id = @SiteId
GROUP BY s.Status
UNION ALL --UNION BACK ON TABLE WITH NOT EXISTS
SELECT 'N/A' AS Status, 0 AS StatusCount
WHERE NOT EXISTS (SELECT 1
   FROM Sites S
   WHERE S.Id = @SiteId
) 

DECLARE @col int; 
select @col = id  FROM site WHERE status = 1; 
select coalesce(@col,0);

You should avoid using expensive methods. You don't need any column for TBL2.

SELECT COUNT(*) FROM(
         SELECT TOP 1     1 AS CNT  FROM  TBL1 
         WHERE ColumnValue ='FooDoo') AS TBL2

Or this code:

IF EXISTS (SELECT TOP 1 1 FROM TABLE1 AS T1 
                          WHERE T1.ColumnValue='VooDoo') 
   SELECT 1 
ELSE 
   SELECT 0
 

You can do something just like this.

IF EXISTS ( SELECT * FROM TableName WHERE Column=colval)
BEGIN
   select 
select name ,Id from TableName WHERE Column=colval
END
ELSE
  SELECT 'test' as name,0 as Id

I liked James Jenkins reply with the ISNULL check, but I think he meant IFNULL. ISNULL does not have a second parameter like his syntax, but IFNULL has the second parameter after the expression being checked to substitute if a NULL is found.