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.

• Martin Smith: Though it's basically the same as the final query in the question with the additional limitation that it doesn't distinguish between a NULL row being stored in the table and no row being stored at all. It still has the issue that MIN/MAX doesn't work with all datatypes.

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]

• Paschover: If you vote down an answer, it would be nice if you explained why.
• Malachi: can't you nest a Select statement in the isnull statement?
• Paschover: No, you need a row returned for that.
• Malachi: I found a way to do it, see my answer
• Paschover: @Malachi: I can't yet comment on your answer, so I will post here. You don't need the top 1 in your query, as there should be a constraint on the table that makes config names unique.

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;