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()).

Does this need to be done in a single query statement? Otherwise you could declare a variable with a default, select into it, then select the variable. — Paul Bellora, Sep 16 '12 at 19:18

K.A.F. · Accepted Answer · 2012-09-16T20:17:28.523

153

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');

edited Sep 16 '12 at 20:17

answered Sep 16 '12 at 20:12

K.A.F.

2,277
1
16
17

3

Good catch to put the whole query in the IFNULL! Actually I tried something similar: SELECT IFNULL(field1, 0) FROM table WHERE id = 123 LIMIT 1; but it was not working (no result), I didn't think of putting the whole query in IFNULL... – Maxime Laval Sep 16 '12 at 20:28
4

This is the best answer on SO to this often asked question. Side note, `COALESCE()` also works in place of `IFNULL()`. Not sure if there's a performance difference but I'm used to `COALESCE()` so it's easier to remember. – But those new buttons though.. Jan 28 '16 at 17:13
`COALESCE()` will continue to find no null value. then has over than one parameter. http://www.techonthenet.com/mysql/functions/coalesce.php – illusionJJ May 03 '16 at 07:46
This requires running the SELECT statement twice. Once to check if it can be found, and another to actually find it. That's double the load on the MySQL server for the exact same results. See solution below by MarkNHopGood . – HoldOffHunger Sep 11 '16 at 22:07
1

Upvote! Never thought about using the `IFNULL` on the whole query! – Ju Oliveira May 23 '17 at 17:08

score 41 · Answer 2 · answered Feb 23 '16 at 11:10

41

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.

answered Feb 23 '16 at 11:10

Mark N Hopgood

843
8
14

1

Extremely useful!! I swapped 'default_value_if_no_record' with "null" (no quotes) and it produced the desired output. Perfect, thanks! – HoldOffHunger Sep 11 '16 at 22:05
1

ingenious my friend! – Oooogi Mar 22 '17 at 13:07
4

Yes, this is a pretty old post, but wanted to mention that `union` does not guarantee any order, so it's entirely possible that the 1 entry which gets returned would be the default row and not the result. I'm not aware of any implementation that would actually shuffle this simple result set, but it's really not safe to depend on that behavior. The [doc page](https://dev.mysql.com/doc/refman/5.7/en/union.html) mentions this and gives a solution involving dummy sorting column if you REALLY wanna use this approach, but it starts getting a little messy. – A C Jun 07 '17 at 01:24
Sometimes mysql can return empty string, and UNION will not catch. Solution: `(SELECT field1 FROM table WHERE id = 123 AND field1 <> '') UNION (SELECT 'default_value_if_no_record') LIMIT 1;` – Widmo Apr 21 '20 at 00:47
AS @AC mentions, UNION does not guarantee order, but if you are trying to return a simple Y or N flag, you can alias the field and use ORDER BY to ensure you get the correct order: `(SELECT IF(field IS NULL, 'N', 'Y') AS flag FROM table WHERE id = 123) UNION (SELECT 'N' AS flag) ORDER BY flag DESC LIMIT 1;` – MarkusMulholland Dec 13 '21 at 09:17

score 21 · Answer 3 · answered Sep 16 '12 at 19:24

21

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

answered Sep 16 '12 at 19:24

Andreas Wederbrand

38,065
11
68
78

Or even: `SELECT 1, field FROM table WHERE ...` – nico Sep 16 '12 at 19:29
No. That won't work. Neither will min, max, sum or most of the other functions. Basically I always stick to count as I know that works. – Andreas Wederbrand Sep 16 '12 at 19:32
Oh, you're right, I was under the impression it worked but in effect I just tried and it does not – nico Sep 16 '12 at 19:35
2

Cant use GROUP by with this one. – PodTech.io May 03 '16 at 10:45
@Andreas Wederbrand actually sum() can work when used correctly...e.g select ifnull(sum(record_number), 13) from bch_trans_file where 7=9; – Uncle Iroh Feb 03 '17 at 17:12
@Uncle Iroh When using group by it won't work. I tried select ifnull(sum(1), 0) from part where 1 = 2 group by part_code; in my database but it returns no row. – Nov 07 '17 at 05:28
@PLAYMAKER - Yeah see Hariboo's comment as well. Group by ruins it. It's fragile to begin with. – Uncle Iroh Nov 07 '17 at 20:26

score 8 · Answer 4 · answered Feb 08 '16 at 10:54

8

You can use COALESCE

SELECT COALESCE(SUM(column),0)
FROM   table

answered Feb 08 '16 at 10:54

Nikunj K.

8,779
4
43
53

daz · Answer 5 · 2021-05-28T05:42:37.030

If someone is looking to use this to insert the result INTO a variable and then using it in a Stored Procedure; you can do it like this:

DECLARE date_created INT DEFAULT 1;
SELECT IFNULL((SELECT date FROM monthly_comission WHERE date = date_new_month LIMIT 1), 0) 
INTO date_created 
WHERE IFNULL((SELECT date FROM monthly_comission WHERE date = date_new_month LIMIT 1), 0) = 0;

With this you're storing in the variable 'date_created' 1 or 0 (if returned nothing).

score 0 · Answer 6 · answered Sep 16 '12 at 19:26

0

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.

answered Sep 16 '12 at 19:26

Suzan Cioc

29,281
63
213
385

score 0 · Answer 7 · answered May 21 '19 at 01:48

0

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.

answered May 21 '19 at 01:48

Kai Liu

121
2
7

score 0 · Answer 8 · answered Jul 23 '20 at 13:42

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

score 0 · Answer 9 · edited Aug 10 '23 at 13:02

0

if you want only one result then you can use aggerate functions. Cause aggerate function print null if there is no value found. I can give you 2 solutions.

solution1,

SELECT
      max(field1)
FROM 
(SELECT field1 FROM table WHERE id = 123);

Solution2,

(SELECT field1 FROM table WHERE id = 123 LIMIT 1)
UNION
(SELECT NULL)
LIMIT 1;

edited Aug 10 '23 at 13:02

toyota Supra

3,181
4
15
19

answered Aug 10 '23 at 12:41

I Q

1
1

Returning a value even if no result

9 Answers9

Linked

Related