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

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.

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.

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.)

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'

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)

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 );

*SQL solution

Suppose you have a review table which has primary key "id".

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.

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 )

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 < 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

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.

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.

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 & 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

this is what I used for getting a default value if no values are present.

select if ((select count(*) from `tbs`.`replication_status`) > 0, (select rs.`last_replication_end_date` from `tbs`.`replication_status` 
          rs where rs.`last_replication_start_date` is not null and rs.`last_replication_end_date` 
          is not null and rs.`table` = '%s' order by id desc limit 1), (select CAST(unix_timestamp(CURRENT_TIMESTAMP(6)) as UNSIGNED))) as ts;