Returning a value even if no result

Question

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

User · Answer

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

User · Answer

You can use COALESCE   SELECT COALESCE SUM column  0  FROM   table

User · Answer

You could include count id   That will always return   select count field1   field1 from table where id   123 limit 1    http   sqlfiddle com   2 64c76 4

User · Answer

MySQL has a function to return a value if the result is null  You can use it on a whole query   SELECT IFNULL   SELECT field1 FROM table WHERE id   123 LIMIT 1    not found

User · Answer

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

User · Answer

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

User · Answer

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

[mysql] Returning a value even if no result

Examples related to mysql