Select first value if exists, otherwise select default value

Question

Given an instance of SQL Server, imagine there's a table named Configuration, which has three columns: ID, Name, and Data. There should be no duplicate rows for Name.

Now imagine that you want to select the data stored for a particular configuration, but if that configuration doesn't have a row in the table, then you just want to select a default value instead.

This, then, is what I have been using:

SELECT CASE
         WHEN EXISTS(SELECT 1
                     FROM   Configuration
                     WHERE  Name = 'NameOfConfiguration')
           THEN (SELECT Data
                 FROM   Configuration
                 WHERE  Name = 'NameOfConfiguration')
         ELSE 'Default Value'
       END 

But that looks bad, so I was trying to think of a way to shorten the EXISTS() call, make the code a little clearer, get rid of duplicate code, and possibly even speed it up, and this is what I've been able to find so far:

SELECT CASE
         WHEN COUNT(1) > 0
           THEN MIN(Data)
         ELSE 'Default Value' 
       END
FROM   Configuration
WHERE  Name = 'NameOfConfiguration' 

Is this latter code snippet the right way to go about this sort of thing in general? Unfortunately, SQL Server requires an aggregate function call or GROUP BY clause to be applied to the latter, and FIRST() evidently doesn't exist in SQL Server. I am not restricting this call to MIN() to any sort of datatype whatsoever. Is there a better way to write this?

Answer 1

The shorter query I can think of is

SELECT COALESCE(MAX(Data), 'Default Value')
FROM   Configuration
WHERE  Name = 'NameOfConfiguration';

If the configuration is present it'll take it. If the configuration is not present the MAX will generate a NULL value (the MAX of nothing is NULL) that will be coalesced to the default value

---

There is a more generic approach that takes care of what said by Martin Smith in his comment, but is more verbose (using the hypothesis that the configuration name are unique)

WITH Param AS (
  SELECT ID, Data
  FROM   Configuration
  WHERE  Name = 'NameOfConfiguration'
  UNION ALL
  SELECT NULL, 'Default Value'
)
SELECT TOP 1
       Data
FROM   Param
ORDER BY ID DESC

The ID of the Default Value is set to NULL to avoid to use a magic number.
From the BOL: NULL values are treated as the lowest possible values.

IMO a general solution should be used only where a specific one is not possible, and that doesn't seems the case. Also in a generic case the Default Value will need to be a valid value for the datatype of the configuration values otherwise the UNION will return a conversion error.

Answer 2

The query feels tortured because SQL is designed to work with sets of data. You're using SQL like an ordinary programming language, trying to turn tables into scalars, and embedding special values in the query rather than storing it as data.

I recommend that you create and populate a ConfigurationDefaults table that is analogous to your Configuration table.

CREATE TABLE Configuration
( ID INTEGER IDENTITY PRIMARY KEY
, Name VARCHAR(123) UNIQUE
, Data VARCHAR(123)
);

CREATE TABLE ConfigurationDefaults
( Name VARCHAR(123) PRIMARY KEY
, Data VARCHAR(123)
);

INSERT INTO ConfigurationDefaults VALUES ('NameOfConfiguration', 'Default Value');

Then, create a view that merges the two, such that the Configuration settings override the defaults.

CREATE VIEW EffectiveConfiguration AS
    SELECT COALESCE(Cfg.Name, Def.Name) AS Name
         , CASE WHEN Cfg.Name IS NULL THEN Def.Data ELSE Cfg.Data END AS Data
        FROM Configuration AS Cfg
            FULL OUTER JOIN ConfigurationDefaults AS Def
                ON Cfg.Name = Def.Name;

Once that infrastructure is in place, the query is a joy to read and write!

SELECT Data FROM EffectiveConfiguration WHERE Name = 'NameOfConfiguration';

Answer 3

If you have a static default value, you can just assign that to a variable and conditionally overwrite if a row is found. This first select returns data1, and the second select returns the default value as the name is not in the table.

create table #cfg (name varchar(32) primary key, id int, data varchar(32))
insert #cfg values ('name1', null, 'data1');
insert #cfg values ('name2', null, 'data2');
go

declare @data varchar(32)
set @data='defaultvalue';
select @data=data
from #cfg
where name='name1';
select @data;
go

declare @data varchar(32)
set @data='defaultvalue';
select @data=data
from #cfg
where name='name3';
select @data;
go

Answer 4

Why don't you just select the field from the table where Name is equal to the NameOfConfiguration and then use an isnull?

DECLARE @GenericVariableName VARCHAR(MAX)
SET @GenericVariableName = (SELECT TOP (1) Data
                             FROM Configuration 
                             WHERE Name = 'NameOfConfiguration')

SELECT isnull(@GenericVariableName, 'Default Value')

this is going to be a lot more straight forward than using the EXIST statement

Answer 5

I would think that the first query you tried would perform better, as it doesn't have to aggregate anything. I believe it's how I would approach it. I would just reformat it so we can read it a little easier.

SELECT CASE 
    WHEN EXISTS(
        SELECT 1 
        FROM Configuration 
        WHERE Name = 'NameOfConfiguration'
    )
    THEN (
        SELECT Data 
        FROM Configuration 
        WHERE Name = 'NameOfConfiguration'
    ) 
    ELSE 'Default Value' 
END

---

There are a couple of other little improvements to be made though. You need to alias this column. Otherwise you'll end up with a mile long name for it. (Depending on your RDBMS. I know SQL Server will name it with the expression string.)

The second thing I would do is declare a variable for 'NameOfConfiguration'. You're using the same parameter in two places, so it makes sense to declare a variable. This will also make things easier if you should decide to wrap this query up in a stored procedure.

Answer 6

This is clearly a case where you would like to use ISNULL. But for that you need to return a row in case the config name doesn't exist. So that in turn implies you need a left outer join. You then create a dummy temp table with the value you are looking for, and left outer join on the actual data.

select
case when c.Name is null then 'Default Value' else c.Data end [Data]
from
(select 'NameOfConfiguration' [Name]) Target
left outer join Configuration c on c.[Name] = Target.[Name]

Answer 7

if you are using the sql developer then it will give you the error:

ORA-00923: FROM keyword not found where expected 00923. 00000 - "FROM keyword not found where expected"

In that case you need to add from dual in the end.

SELECT CASE 
    WHEN EXISTS(
        SELECT 1 
        FROM your_table 
        WHERE your_item= 'XXX' 
    )
    THEN (
        SELECT some_value
        FROM your_table 
        WHERE your_item= 'XXX' 
    ) 
    ELSE '0' END from dual;