CODEWITHSUNDEEP
sqlsqlite
ort11
ort11

Reputation: 3349

SQL SQLITE using select to return a row when select has no rows

Need to return a "canned" row when there is no rows in a select. So something like

select col1 from table where col1 = 'something'

if this returns 1 row or greater fine, but if it returns no rows, need to return something like 'nothing' as col1. There are reasons for my madness....

I have tried 

select 
 case when col1 is null 
 then 
  'nothing' 
 else 
  col1 
 end as col1 
from table where col1 = 'something'

But this will not return 'nothing' since there are no rows to process.....

Upvotes: 0

Views: 2004

Answers (4)

PinnyM
PinnyM

Reputation: 35533

Adding a RIGHT JOIN should get you what you need:

select COALESCE(col1, fake.filler) as col1
from table 
  right join (select 'nothing' as [filler]) fake 
  on col1 = 'something'

Or as LEFT JOIN (less elegant):

select COALESCE(col1, fake.filler) as col1
from (select 'nothing' as [filler]) fake 
  left join table
  on col1 = 'something'

Admittedly, @Joe Stefanelli is correct that this behavior doesn't belong in the data retrieval layer. But if you absolutely must...

Upvotes: 1

Florin Ghita
Florin Ghita

Reputation: 17643

In Oracle I would write this:

select col1 from table 
where col1 = 'something'
union all
select 'Nothing' from dual 
where not exists (select 1 from table where col1 = 'something')

I don't know how to emulate dual in sqlite.

Update: As I see, from can be ommited, so the answer can be:

select col1 from table 
where col1 = 'something'
union all
select 'Nothing'  
where not exists (select 1 from table where col1 = 'something')

Upvotes: 0

juergen d
juergen d

Reputation: 204766

Only thing I can come up with is

if exists(select col1 from table where col1 = 'something') 
select col1 from table where col1 = 'something'
else 'nothing'

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135808

In my opinion, this is better handled in your application code rather than trying to force an artificial value in on the database side. Simply test for zero rows in the result set and make your substitution at that time.

Upvotes: 0

Related Questions