How to set a default row for a query that returns no rows

Question

I need to know how to return a default row if no rows exist in a table  What would be the best way to do this  I m only returning a single column from this particular table to get its value    Edit  This would be SQL Server

User · Answer

Do you want to return a full row   Does the default row need to have default values or can it be an empty row   Do you want the default row to have the same column structure as the table in question   Depending on your requirements  you might do something like this   1   run the query and put results in a temp table  or table variable  2   check to see if the temp table has results 3   if not  return an empty row by performing a select statement similar to this  in SQL Server    select    as columnA     as columnB     as columnC from  tempTable   Where columnA  columnB and columnC are your actual column names

User · Answer

This would be eliminate the select query from running twice and be better for performance   Declare  rate int  select       rate   rate  from      d payment index where      fy   2007     and payment year   2008     and program id   18  IF   rowcount   0     Set  rate   0  Select  rate  rate

User · Answer

One approach for Oracle   SELECT val FROM myTable UNION ALL SELECT  DEFAULT  FROM dual WHERE NOT EXISTS  SELECT   FROM myTable    Or alternatively in Oracle   SELECT NVL MIN val    DEFAULT   FROM myTable   Or alternatively in SqlServer   SELECT ISNULL MIN val    DEFAULT   FROM myTable   These use the fact that MIN   returns NULL when there are no rows

User · Answer

This would be eliminate the select query from running twice and be better for performance   Declare  rate int  select       rate   rate  from      d payment index where      fy   2007     and payment year   2008     and program id   18  IF   rowcount   0     Set  rate   0  Select  rate  rate

User · Answer

How about this   SELECT DEF Rate  ACTUAL Rate  COALESCE ACTUAL Rate  DEF Rate  AS UseThisRate FROM     SELECT 0  DEF  Rate  -- This is your default rate LEFT JOIN     select rate    from d payment index   --WHERE 1 2   -- Uncomment this line to simulate a missing value    --   HERE IF YOUR ACTUAL WHERE CLAUSE  Removed for testing purposes      --where fy   2007   -- and payment year   2008   --  and program id   18   ACTUAL  Rate  ON 1 1   Results  Valid Rate Exists  Rate        Rate        UseThisRate ----------- ----------- ----------- 0           1           1   Default Rate Used  Rate        Rate        UseThisRate ----------- ----------- ----------- 0           NULL        0   Test DDL  CREATE TABLE d payment index  rate int NOT NULL  INSERT INTO d payment index VALUES  1

User · Answer

One table scan method using a left join from defaults to actuals   CREATE TABLE  stackoverflow-285666   k int  val varchar 255    INSERT  INTO  stackoverflow-285666  VALUES   1   1-1   INSERT  INTO  stackoverflow-285666  VALUES   1   1-2   INSERT  INTO  stackoverflow-285666  VALUES   1   1-3   INSERT  INTO  stackoverflow-285666  VALUES   2   2-1   INSERT  INTO  stackoverflow-285666  VALUES   2   2-2    DECLARE  k AS int SET  k   0  WHILE  k  lt  3     BEGIN         SELECT   k AS k                 COALESCE ActualValue  DefaultValue  AS  Value          FROM                       SELECT  DefaultValue  AS DefaultValue                   AS Defaults         LEFT JOIN                      SELECT   val AS ActualValue                    FROM      stackoverflow-285666                     WHERE    k    k                     AS  Values                  ON 1   1          SET  k    k   1     END  DROP TABLE  stackoverflow-285666    Gives output   k           Value ----------- ------------ 0           DefaultValue  k           Value ----------- ------------ 1           1-1 1           1-2 1           1-3  k           Value ----------- ------------ 2           2-1 2           2-2

User · Answer

If your base query is expected to return only one row  then you could use this trick   select NVL  MIN rate   0   AS rate  from d payment index where fy   2007   and payment year   2008   and program id   18    Oracle code  not sure if NVL is the right function for SQL Server

User · Answer

I figured it out  and it should also work for other systems too  It s a variation of WW s answer   select rate  from d payment index where fy   2007   and payment year   2008   and program id   18 union select 0 as rate  from d payment index  where not exists  select rate                    from d payment index                   where fy   2007                     and payment year   2008                     and program id   18

User · Answer

I figured it out  and it should also work for other systems too  It s a variation of WW s answer   select rate  from d payment index where fy   2007   and payment year   2008   and program id   18 union select 0 as rate  from d payment index  where not exists  select rate                    from d payment index                   where fy   2007                     and payment year   2008                     and program id   18

User · Answer

One table scan method using a left join from defaults to actuals   CREATE TABLE  stackoverflow-285666   k int  val varchar 255    INSERT  INTO  stackoverflow-285666  VALUES   1   1-1   INSERT  INTO  stackoverflow-285666  VALUES   1   1-2   INSERT  INTO  stackoverflow-285666  VALUES   1   1-3   INSERT  INTO  stackoverflow-285666  VALUES   2   2-1   INSERT  INTO  stackoverflow-285666  VALUES   2   2-2    DECLARE  k AS int SET  k   0  WHILE  k  lt  3     BEGIN         SELECT   k AS k                 COALESCE ActualValue  DefaultValue  AS  Value          FROM                       SELECT  DefaultValue  AS DefaultValue                   AS Defaults         LEFT JOIN                      SELECT   val AS ActualValue                    FROM      stackoverflow-285666                     WHERE    k    k                     AS  Values                  ON 1   1          SET  k    k   1     END  DROP TABLE  stackoverflow-285666    Gives output   k           Value ----------- ------------ 0           DefaultValue  k           Value ----------- ------------ 1           1-1 1           1-2 1           1-3  k           Value ----------- ------------ 2           2-1 2           2-2

User · Answer

I figured it out  and it should also work for other systems too  It s a variation of WW s answer   select rate  from d payment index where fy   2007   and payment year   2008   and program id   18 union select 0 as rate  from d payment index  where not exists  select rate                    from d payment index                   where fy   2007                     and payment year   2008                     and program id   18

User · Answer

This snippet uses Common Table Expressions to reduce redundant code and to improve readability  It is a variation of John Baughman s answer    The syntax is for SQL Server    WITH products AS               SELECT prod name                     price               FROM Products Table              WHERE prod name LIKE   foo                defaults AS               SELECT  -  AS prod name                     0   AS price         SELECT   FROM products UNION ALL SELECT   FROM defaults  WHERE NOT EXISTS   SELECT   FROM products

User · Answer

Do you want to return a full row   Does the default row need to have default values or can it be an empty row   Do you want the default row to have the same column structure as the table in question   Depending on your requirements  you might do something like this   1   run the query and put results in a temp table  or table variable  2   check to see if the temp table has results 3   if not  return an empty row by performing a select statement similar to this  in SQL Server    select    as columnA     as columnB     as columnC from  tempTable   Where columnA  columnB and columnC are your actual column names

User · Answer

If your base query is expected to return only one row  then you could use this trick   select NVL  MIN rate   0   AS rate  from d payment index where fy   2007   and payment year   2008   and program id   18    Oracle code  not sure if NVL is the right function for SQL Server

User · Answer

Insert your default values into a table variable  then update this tableVar s single row with a match from your actual table  If a row is found  tableVar will be updated  if not  the default value remains  Return the table variable       ---    The table  amp  its data     CREATE TABLE dbo Rates           PkId int          name varchar 10           rate decimal 10 2            INSERT INTO dbo Rates PkId  name  rate  VALUES  1   Schedule 1   0 1      INSERT INTO dbo Rates PkId  name  rate  VALUES  2   Schedule 2   0 2    Here s the solution   ---    The solution  CREATE PROCEDURE dbo GetRate     PkId int AS BEGIN   DECLARE  tempTable TABLE       PkId int       name varchar 10        rate decimal 10 2       ---  1  Insert default values into  tempTable  PkId 0 is dummy value    INSERT INTO  tempTable PkId  name  rate  VALUES  0   DEFAULT   0 00    ---  2  Update the single row in  tempTable with the actual value   ---     This only happens if a match is found  UPDATE  tempTable     SET t PkId x PkId  t name x name  t rate   x rate     FROM  tempTable t INNER JOIN dbo Rates x     ON t PkId   0     WHERE x PkId    PkId   SELECT   FROM  tempTable END   Test the code   EXEC dbo GetRate  PkId 1     --- returns values for PkId 1 EXEC dbo GetRate  PkId 12314 --- returns default values

User · Answer

I figured it out  and it should also work for other systems too  It s a variation of WW s answer   select rate  from d payment index where fy   2007   and payment year   2008   and program id   18 union select 0 as rate  from d payment index  where not exists  select rate                    from d payment index                   where fy   2007                     and payment year   2008                     and program id   18

User · Answer

SQL solution Suppose you have a review table which has primary key  quot id quot   SELECT   FROM review WHERE id   1555 UNION ALL SELECT   FROM review WHERE NOT EXISTS   SELECT   FROM review where id   1555   AND id   1  if table doesn t have review with 1555 id then this query will provide a review of id 1

User · Answer

One table scan method using a left join from defaults to actuals   CREATE TABLE  stackoverflow-285666   k int  val varchar 255    INSERT  INTO  stackoverflow-285666  VALUES   1   1-1   INSERT  INTO  stackoverflow-285666  VALUES   1   1-2   INSERT  INTO  stackoverflow-285666  VALUES   1   1-3   INSERT  INTO  stackoverflow-285666  VALUES   2   2-1   INSERT  INTO  stackoverflow-285666  VALUES   2   2-2    DECLARE  k AS int SET  k   0  WHILE  k  lt  3     BEGIN         SELECT   k AS k                 COALESCE ActualValue  DefaultValue  AS  Value          FROM                       SELECT  DefaultValue  AS DefaultValue                   AS Defaults         LEFT JOIN                      SELECT   val AS ActualValue                    FROM      stackoverflow-285666                     WHERE    k    k                     AS  Values                  ON 1   1          SET  k    k   1     END  DROP TABLE  stackoverflow-285666    Gives output   k           Value ----------- ------------ 0           DefaultValue  k           Value ----------- ------------ 1           1-1 1           1-2 1           1-3  k           Value ----------- ------------ 2           2-1 2           2-2

User · Answer

If your base query is expected to return only one row  then you could use this trick   select NVL  MIN rate   0   AS rate  from d payment index where fy   2007   and payment year   2008   and program id   18    Oracle code  not sure if NVL is the right function for SQL Server

User · Answer

How about this   SELECT DEF Rate  ACTUAL Rate  COALESCE ACTUAL Rate  DEF Rate  AS UseThisRate FROM     SELECT 0  DEF  Rate  -- This is your default rate LEFT JOIN     select rate    from d payment index   --WHERE 1 2   -- Uncomment this line to simulate a missing value    --   HERE IF YOUR ACTUAL WHERE CLAUSE  Removed for testing purposes      --where fy   2007   -- and payment year   2008   --  and program id   18   ACTUAL  Rate  ON 1 1   Results  Valid Rate Exists  Rate        Rate        UseThisRate ----------- ----------- ----------- 0           1           1   Default Rate Used  Rate        Rate        UseThisRate ----------- ----------- ----------- 0           NULL        0   Test DDL  CREATE TABLE d payment index  rate int NOT NULL  INSERT INTO d payment index VALUES  1

User · Answer

This would be eliminate the select query from running twice and be better for performance   Declare  rate int  select       rate   rate  from      d payment index where      fy   2007     and payment year   2008     and program id   18  IF   rowcount   0     Set  rate   0  Select  rate  rate

User · Answer

One table scan method using a left join from defaults to actuals   CREATE TABLE  stackoverflow-285666   k int  val varchar 255    INSERT  INTO  stackoverflow-285666  VALUES   1   1-1   INSERT  INTO  stackoverflow-285666  VALUES   1   1-2   INSERT  INTO  stackoverflow-285666  VALUES   1   1-3   INSERT  INTO  stackoverflow-285666  VALUES   2   2-1   INSERT  INTO  stackoverflow-285666  VALUES   2   2-2    DECLARE  k AS int SET  k   0  WHILE  k  lt  3     BEGIN         SELECT   k AS k                 COALESCE ActualValue  DefaultValue  AS  Value          FROM                       SELECT  DefaultValue  AS DefaultValue                   AS Defaults         LEFT JOIN                      SELECT   val AS ActualValue                    FROM      stackoverflow-285666                     WHERE    k    k                     AS  Values                  ON 1   1          SET  k    k   1     END  DROP TABLE  stackoverflow-285666    Gives output   k           Value ----------- ------------ 0           DefaultValue  k           Value ----------- ------------ 1           1-1 1           1-2 1           1-3  k           Value ----------- ------------ 2           2-1 2           2-2

User · Answer

SQL solution Suppose you have a review table which has primary key  quot id quot   SELECT   FROM review WHERE id   1555 UNION ALL SELECT   FROM review WHERE NOT EXISTS   SELECT   FROM review where id   1555   AND id   1  if table doesn t have review with 1555 id then this query will provide a review of id 1

User · Answer

This would be eliminate the select query from running twice and be better for performance   Declare  rate int  select       rate   rate  from      d payment index where      fy   2007     and payment year   2008     and program id   18  IF   rowcount   0     Set  rate   0  Select  rate  rate

User · Answer

Do you want to return a full row   Does the default row need to have default values or can it be an empty row   Do you want the default row to have the same column structure as the table in question   Depending on your requirements  you might do something like this   1   run the query and put results in a temp table  or table variable  2   check to see if the temp table has results 3   if not  return an empty row by performing a select statement similar to this  in SQL Server    select    as columnA     as columnB     as columnC from  tempTable   Where columnA  columnB and columnC are your actual column names

User · Answer

This snippet uses Common Table Expressions to reduce redundant code and to improve readability  It is a variation of John Baughman s answer    The syntax is for SQL Server    WITH products AS               SELECT prod name                     price               FROM Products Table              WHERE prod name LIKE   foo                defaults AS               SELECT  -  AS prod name                     0   AS price         SELECT   FROM products UNION ALL SELECT   FROM defaults  WHERE NOT EXISTS   SELECT   FROM products

User · Answer

One approach for Oracle   SELECT val FROM myTable UNION ALL SELECT  DEFAULT  FROM dual WHERE NOT EXISTS  SELECT   FROM myTable    Or alternatively in Oracle   SELECT NVL MIN val    DEFAULT   FROM myTable   Or alternatively in SqlServer   SELECT ISNULL MIN val    DEFAULT   FROM myTable   These use the fact that MIN   returns NULL when there are no rows

User · Answer

Assuming there is a table config with unique index on config code column  CONFIG CODE     PARAM1   PARAM2 --------------- -------- -------- default config  def      000 config1         abc      123 config2         def      456  This query returns line for config1 values  because it exists in the table  SELECT     FROM  SELECT             FROM config          WHERE config code    config1              OR config code    default config           ORDER BY CASE config code WHEN  default config  THEN 999 ELSE 1 END   WHERE rownum   1   CONFIG CODE     PARAM1   PARAM2 --------------- -------- -------- config1         abc      123  This one returns default record as config3 doesn t exist in the table  SELECT     FROM  SELECT             FROM config          WHERE config code    config3              OR config code    default config           ORDER BY CASE config code WHEN  default config  THEN 999 ELSE 1 END   WHERE rownum   1   CONFIG CODE     PARAM1   PARAM2 --------------- -------- -------- default config  def      000  In comparison with other solutions this one queries table config only once

User · Answer

One approach for Oracle   SELECT val FROM myTable UNION ALL SELECT  DEFAULT  FROM dual WHERE NOT EXISTS  SELECT   FROM myTable    Or alternatively in Oracle   SELECT NVL MIN val    DEFAULT   FROM myTable   Or alternatively in SqlServer   SELECT ISNULL MIN val    DEFAULT   FROM myTable   These use the fact that MIN   returns NULL when there are no rows

User · Answer

If your base query is expected to return only one row  then you could use this trick   select NVL  MIN rate   0   AS rate  from d payment index where fy   2007   and payment year   2008   and program id   18    Oracle code  not sure if NVL is the right function for SQL Server

User · Answer

Insert your default values into a table variable  then update this tableVar s single row with a match from your actual table  If a row is found  tableVar will be updated  if not  the default value remains  Return the table variable       ---    The table  amp  its data     CREATE TABLE dbo Rates           PkId int          name varchar 10           rate decimal 10 2            INSERT INTO dbo Rates PkId  name  rate  VALUES  1   Schedule 1   0 1      INSERT INTO dbo Rates PkId  name  rate  VALUES  2   Schedule 2   0 2    Here s the solution   ---    The solution  CREATE PROCEDURE dbo GetRate     PkId int AS BEGIN   DECLARE  tempTable TABLE       PkId int       name varchar 10        rate decimal 10 2       ---  1  Insert default values into  tempTable  PkId 0 is dummy value    INSERT INTO  tempTable PkId  name  rate  VALUES  0   DEFAULT   0 00    ---  2  Update the single row in  tempTable with the actual value   ---     This only happens if a match is found  UPDATE  tempTable     SET t PkId x PkId  t name x name  t rate   x rate     FROM  tempTable t INNER JOIN dbo Rates x     ON t PkId   0     WHERE x PkId    PkId   SELECT   FROM  tempTable END   Test the code   EXEC dbo GetRate  PkId 1     --- returns values for PkId 1 EXEC dbo GetRate  PkId 12314 --- returns default values

User · Answer

Do you want to return a full row   Does the default row need to have default values or can it be an empty row   Do you want the default row to have the same column structure as the table in question   Depending on your requirements  you might do something like this   1   run the query and put results in a temp table  or table variable  2   check to see if the temp table has results 3   if not  return an empty row by performing a select statement similar to this  in SQL Server    select    as columnA     as columnB     as columnC from  tempTable   Where columnA  columnB and columnC are your actual column names

User · Answer

One approach for Oracle   SELECT val FROM myTable UNION ALL SELECT  DEFAULT  FROM dual WHERE NOT EXISTS  SELECT   FROM myTable    Or alternatively in Oracle   SELECT NVL MIN val    DEFAULT   FROM myTable   Or alternatively in SqlServer   SELECT ISNULL MIN val    DEFAULT   FROM myTable   These use the fact that MIN   returns NULL when there are no rows

User · Answer

Assuming there is a table config with unique index on config code column  CONFIG CODE     PARAM1   PARAM2 --------------- -------- -------- default config  def      000 config1         abc      123 config2         def      456  This query returns line for config1 values  because it exists in the table  SELECT     FROM  SELECT             FROM config          WHERE config code    config1              OR config code    default config           ORDER BY CASE config code WHEN  default config  THEN 999 ELSE 1 END   WHERE rownum   1   CONFIG CODE     PARAM1   PARAM2 --------------- -------- -------- config1         abc      123  This one returns default record as config3 doesn t exist in the table  SELECT     FROM  SELECT             FROM config          WHERE config code    config3              OR config code    default config           ORDER BY CASE config code WHEN  default config  THEN 999 ELSE 1 END   WHERE rownum   1   CONFIG CODE     PARAM1   PARAM2 --------------- -------- -------- default config  def      000  In comparison with other solutions this one queries table config only once

[sql] How to set a default row for a query that returns no rows?

Examples related to sql

Examples related to sql-server