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.

Answer 1

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.

Answer 2

In Oracle:

SELECT val
FROM myTable
UNION ALL
SELECT 'DEFAULT'
FROM dual
WHERE NOT EXISTS (SELECT * FROM myTable)

Answer 3

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 )

Answer 4

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'

Answer 5

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

Answer 6

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

Answer 7

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)