Return a value if no rows are found in Microsoft tSQL

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

SELECT CASE WHEN COUNT 1   gt  0 THEN 1 ELSE 0 END AS  Value   FROM Sites S  WHERE S Id    SiteId and S Status   1 AND         S WebUserId    WebUserId OR S AllowUploads   1

User · Answer

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

User · Answer

DECLARE  col int   select  col   id  FROM site WHERE status   1   select coalesce  col 0

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to tsql