Return Default value if no row found

Question

I'm building query to search multiple rows from database like

SELECT 
  * 
FROM
  table1 
WHERE col1 = '012311' 
  OR col1 = '0123631' 
  OR col1 = '091233' 
  OR col1 = '092111'

Here it returns first 3 values because they are present in the table but it returns nothing for last one because it's not in table. So how can I set my default value in the query if no row is found for that value?

Sounds like you need MySQL `CASE`: http://dev.mysql.com/doc/refman/5.0/en/case.html — Jongosi, May 09 '14 at 06:55

Jongosi · Answer 1 · 2014-05-09T09:10:14.940

11

There are several approaches, which depend on the dataset. Here's one way:

SELECT *, 
IFNULL(
    ( SELECT col1 FROM table1 
        WHERE col1 IN ('012311','0123631','091233','092111') 
    ),
    'some_value'
) AS my_col1
FROM table1;

Not neccessarily copy+paste, you will have to adjust for your specific case.

edited May 09 '14 at 09:10

answered May 09 '14 at 07:11

Jongosi

2,305
1
28
31

Not working . . . .. It's not returning anything when no row found for value – Jadeja RJ May 09 '14 at 07:21

ashish · Answer 2 · 2014-05-09T09:44:44.577

11

In MySQL you can use IFNULL to return a specified value if no row found i.e. when it returns NULL ex-

SELECT IFNULL( (SELECT col1 FROM table1 WHERE col1 in (your_list)) ,'default_value_you_want_to_return');

you can see examples of IFNULL here - IFNULL Example

edited May 09 '14 at 09:44

answered May 09 '14 at 09:29

ashish

211
1
7

It's returning error "Operand should contain 1 column(s)" – Jadeja RJ May 09 '14 at 09:37
1

Using `SELECT * FROM` in sub query is wrong and slows the whole query down in any case. – Jongosi May 09 '14 at 09:39
you can't use this in an INSERT SELECT if nested query is on same table – Pr Shadoko May 23 '15 at 14:20

score 3 · Answer 3 · answered Jun 04 '21 at 14:40

The best way I've founded is this:

SELECT COALESCE(col1, 'defaultValue') col1, COUNT(*) cRows 
FROM table1 
WHERE colx = 'filter';

This query returns 2 columns:

The first is the value of the column searched with the default value set in case the row does not exist.
The second column returns the number of rows with the filter in the table

score 3 · Answer 4 · answered Apr 18 '22 at 22:39

3

Use UNION

SELECT col_x FROM table_x
UNION
SELECT 'default_value';

'default_value' can be of any datatype.

answered Apr 18 '22 at 22:39

Mooze

436
5
9

This is the one that works. The coalesce approach won't work if no initial row is returned. – Vitalis Mar 05 '23 at 09:28

score 2 · Answer 5 · answered Jan 13 '17 at 00:48

I want to chime in on this +2.5yo question and make the assumption, based on the WHERE clause, that Jadeja RJ only wants to return four rows and not the entire table (perhaps the table has 100,000 rows making the resultset very bloated).

I don't want to use the greedy * in the SELECT, so I will use col1 as the row identifier and col2 as its associated value. Of course, more columns can be specified as needed. Here is one way to hard code the desired number of rows and flag the ones that are missing:

SELECT hardrows.col1,IFNULL(table1.col2,'Not Found')
FROM 
(
    (SELECT '012311' AS `col1`) UNION 
    (SELECT '0123631') UNION 
    (SELECT '091233') UNION 
    (SELECT '092111')
) AS `hardrows`
LEFT JOIN `table1`
    ON hardrows.col1=table1.col1;

Granted, I am running on multiple assumptions with this suggestion. I don't know how many rows are in the table, how many hard coded rows need to be declared, nor if there are additional clauses/expressions to the actual query that may render it inefficient. The advantage is purely in controlling the number of rows returned.

Lastly, if the array of identifiers is being supplied by another database table, then that table should replace hardrows.

Thanks. It worked for me except that I used `UNION ALL` instead of just `UNION`. None of the other answers worked. — VHS, Dec 01 '22 at 02:05

score 0 · Answer 6 · answered Jan 29 '23 at 17:11

I am writing for a sub-scenario of the question. I am posting one solution that works for ALL of You THAT HAVE GROUPED QUERY and who wandered like me how to implement the logic to have a row with default values if the query with WHERE CLAUSE returns NO ROWS in CASE 1 and in CASE 2 I will show you some approach of filtering more than ONE ROW.

Hope it will help.

CASE 1

One approach is to use UNION operator between set of default values and set from filtered rows you want.

You can do this:

Create SELECT Statement with your default values.
Then UNION the SELECT Statement from 1. with your desired QUERY (add some WHERE clause, GROUP BY, whatever you want). For demonstration purposes, we will query the id column.
Add column row_number where the default set row_number = 0 and the queried set row_number = 1.
Order by the row_number column in DESC order (in order to have always in first place the filtered rows: order by row_number desc
Then QUERY the whole statement and LIMIT the result by 1.
- this will return the filtered row (if any) or the default row. RESULT with FILTERED ROWS

|  id  |   avg_column_val   |    row_number    |
------------------------------------------------
|  80  |          1         |        1         |
|   0  |          0         |        0         |

WHEN you LIMIT 1 you will get the filtered rows

or

RESULT with DEFAULT ROWS

|  id  |   avg_column_val   |   row_number   |
----------------------------------------------
|   0  |          0         |       0        |

WHEN you LIMIT 1 you will get the default rows

FINAL QUERY

select * from (
        select 0 as id, 0 as avg_column_val, 0 as row_number 
            union
        select t.id, avg(some_column) as avg_column_val, 1 as row_number
        from t_table as t 
        where t.id = @p_id
        group by t.id
        order by row_number desc
    ) as temp 
    limit 1;

CASE 2

Of course, if you want more rows to be returned from WHERE CLAUSE, you can execute your query and COUNT(*) the result to check if there are ANY rows.

Then you can limit by the counted rows and it will return them: (+1 is in case where @p_counted_rows = 0 and you want to return the default values)

Note that you SHOULD set SQL_SELECT_LIMIT to your counted query (Set SQL_SELECT_LIMIT = @p_counted_rows + 1;)-> This will LIMIT the exact number of rows WITHOUT writing LIMIT CLAUSE. When you finish your query - SET SQL_SELECT_LIMIT to DEFAULT (Set SQL_SELECT_LIMIT = Default;)

- This needs to be done because **LIMIT clause cannot use variable**.

FINAL QUERY

set @p_counted_rows = 0;

select @p_counted_rows = count(*)
from 
t_table as t
where t.id = @p_id;

Set SQL_SELECT_LIMIT = @p_counted_rows + 1;

select * from (
        select 0 as id, 0 as avg_column_val, 0 as row_number 
            union
        select t.id, avg(some_column) as avg_column_val, 1 as row_number
        from t_table as t 
        where t.id = @p_id
        order by row_number desc
    ) as temp;

Set SQL_SELECT_LIMIT = Default;

Hope this helps everyone.

Cheers, Happy coding!

score -1 · Answer 7 · edited Jun 05 '21 at 22:12

-1

SELECT
     CASE WHEN col1 = NULL THEN "NULL" ELSE col1 END AS 'col1'
FROM table1
WHERE
     col1 = '012311'
     OR col1 = '0123631'
     OR col1 = '091233'
     OR col1 = '092111'

This might be along the lines of what you're looking for, format-wise. If col1 is NULL then return a string "NULL" (this can be substituted). If col1 is NOT NULL then return the valued result.

edited Jun 05 '21 at 22:12

desertnaut

57,590
26
140
166

answered Jan 10 '18 at 17:03

JRH

39
8

Return Default value if no row found

7 Answers7

Use UNION

CASE 1

CASE 2

Linked