How to Display a Default Value When No Match Found in a Query

How to display a default value when no match found in a query?

SELECT COALESCE((SELECT empname FROM employee WHERE id = 100), 'Unavailable')
FROM   DUAL;

You have to wrap the SELECT into another SELECT or no row will be returned. And where there is no row, there cannot be a value.

How to return default row if some values do not match?

SELECT * FROM mydata
WHERE (coalesce(a, user_a), coalesce(b, user_b)) = (user_a, user_b)
ORDER BY a IS NULL, b IS NULL
LIMIT 1;

Here user_a and user_b are the values for which you query.

SQL query to return default value for a column if no Match in two different tables

Left join table1 with table2 and use coalesce to replace NULL values (when right table has no match) with the default value:

select t1.id, 
       t1.name, 
       coalesce(t2.result, 'fail') as Result      
from Table_1 as t1
left outer join Table_2 as t2 on t1.name = t2.name

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.

Return default value if no results found in SQL query

While this would probably be easier to handle with your scripting language, you could do a union all to get the default value since you're ordering by the id field:

SELECT id, picture 
FROM media 
WHERE userID = $friend->user_id 
    AND relation = 'profile_picture' 
UNION ALL 
SELECT -1 id, 'default.png' picture
ORDER BY id DESC LIMIT 1

• SQL Fiddle Demo

Display default value if query results in no records in BigQuery

Option 1

Below displays row with all nulls in case if there is no result returned for your_query

#standardSQL
WITH your_query AS ( ... )
SELECT * FROM your_query 
UNION ALL 
SELECT your_query.* REPLACE ("No results found" AS result)
FROM (SELECT 1) 
LEFT JOIN your_query ON FALSE
WHERE NOT EXISTS (SELECT 1 FROM your_query)  

Row result              number  message  
1   No results found    null    null     

Option 2

If you know in advance output schema - below returns default row (assuming 0 default for number and "none" default for message

#standardSQL
WITH your_query AS ( ... )
SELECT * FROM your_query 
UNION ALL 
SELECT "No results found", 0, "none" FROM (SELECT 1) 
LEFT JOIN your_query ON FALSE
WHERE NOT EXISTS (SELECT 1 FROM your_query)

Row result              number  message  
1   No results found    0       none     

---

Related Topics