How to Set a Default Row for a Query That Returns No Rows

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

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.

Return a default value if single row is not found

One way to do it

SELECT IFNULL(MIN(`file`), 'default.webm') `file` 
  FROM `show`, `schedule` 
 WHERE `channel` = 1 AND `start_time` <= UNIX_TIMESTAMP() 
   AND `start_time` > UNIX_TIMESTAMP()-1800 AND `show`.`id` = `schedule`.`file` 
 ORDER BY `start_time` DESC LIMIT 1

Since you return only one row, you can use an aggregate function, in that case MIN(), that ensures that you'll get NULL if no records selected. Then IFNULL() or COALESCE() will do its job.

MYSQL query that returns a default row if no other row is matched

One method is:

select c.*
from cities c
where c.city in (?, '(Other)')
order by c.city = '(Other)'
limit 1;

This retrieves the two rows possible rows that might match (? is a placeholder for the name you want). If there are two rows, then then one that is not "other" is chosen.

Select default if no rows return

You can use COALESCE

Evaluates the arguments in order and returns the current value of the
first expression that initially does not evaluate to NULL.

SELECT COALESCE((SELECT TOP 1 Column1 FROM Table1 WHERE PK = SomeValue), 'DefaultValue')

or like this:

DECLARE @ReturnValue INT = 3 -- Default value
SELECT TOP 1 @ReturnValue = Column1 FROM Table1 WHERE PK = SomeValue
SELECT @ReturnValue

Return default row, if no row found SQL

Maybe use a EXISTS, ideally with a correlated query, but don't know your table structures.

SELECT  TOP 1 * FROM [table] WHERE NOT EXISTS (SELECT * FROM [table] WHERE ID > 18)

You should also add a ORDER BY if you use TOP 1

mysql return default row if no row is found

One way to accomplish this is by combining two queries with a UNION.

The first query fetches the results for the given attribute, and the second query fetches the default results for IDs that do not have the given attribute.

For example:

select id, value 
from your_table
where attr = 'attr1'
union 
select t1.id, t1.value 
from your_table t1
where t1.attr = 'default'
and not exists (select NULL from your_table t2 where t2.id = t1.id and t2.attr = 'attr1')

MySql, return default value if no rows returned

You want a left join:

select coalesce(cm.cap_id, 0) as cap_id
from (select 'Type1' as cap_type, 'CapDesc1' as cap_desc union all
      select 'Type2' as cap_type, 'CapDesc2' as cap_desc union all
      select 'Type3' as cap_type, 'CapDesc3' as cap_desc union all
      select 'Type4' as cap_type, 'CapDesc4' as cap_desc
     ) c left join
     cap_master cm
     on cm.cap_type = c.cap_type and cm.cap_desc = c.cap_desc
order by c.cap_type, c.cap_desc;

If you need to support NULL cap_desc (which is not part of the original question), you can do:

select coalesce(cm.cap_id, 0) as cap_id
from (
      select 'Type5' as cap_type, null as cap_desc
     ) c left join
     cap_master cm
     on cm.cap_type = c.cap_type and 
        (cm.cap_desc = c.cap_desc or cm.cap_desc is null and c.cap_desc is null)
order by c.cap_type, c.cap_desc;

Here is a SQL Fiddle.

---

Related Topics