I have this kind of simple query that returns a not null integer field for a given id:
SELECT field1 FROM table WHERE id = 123 LIMIT 1;
The thing is if the id is not found, the resultset is empty. I need the query to always return a value, even if there is no result.
I have this thing working but I don't like it because it runs 2 times the same subquery:
SELECT IF(EXISTS(SELECT 1 FROM table WHERE id = 123) = 1, (SELECT field1 FROM table WHERE id = 123 LIMIT 1), 0);
It returns either field1 if the row exists, otherwise 0. Any way to improve that?
Thanks!
Edit following some comments and answers: yes it has to be in a single query statement and I can not use the count trick because I need to return only 1 value (FYI I run the query with the Java/Spring method SimpleJdbcTemplate.queryForLong()).
This question is related to
mysql
You could include count(id). That will always return.
select count(field1), field1 from table where id = 123 limit 1;
You can use COALESCE
SELECT COALESCE(SUM(column),0)
FROM table
As you are looking for 1 record, (LIMIT 1) then this will work.
(SELECT field1 FROM table WHERE id = 123)
UNION
(SELECT 'default_value_if_no_record')
LIMIT 1;
Can be a handy way to display default values, or indicate no results found. I use it for reports.
See also http://blogs.uoregon.edu/developments/2011/03/31/add-a-header-row-to-mysql-query-results/ for a way to use this to create headers in reports.
k-a-f's answer works for selecting one column, if selecting multiple column, we can.
DECLARE a BIGINT DEFAULT 1;
DECLARE b BIGINT DEFAULT "name";
SELECT id, name from table into a,b;
Then we just need to check a,b for values.
Do search with LEFT OUTER JOIN. I don't know if MySQL allows inline VALUES in join clauses but you can have predefined table for this purposes.
if you want both always a return value but never a null value you can combine count with coalesce :
select count(field1), coalesce(field1,'any_other_default_value') from table;
that because count, will force mysql to always return a value (0 if there is no values to count) and coalesce will force mysql to always put a value that is not null
Source: Stackoverflow.com