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?

Answer 1

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.

Answer 2

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

Answer 3

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

Answer 4

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.

Answer 5

Use UNION

SELECT col_x FROM table_x
UNION
SELECT 'default_value';

'default_value' can be of any datatype.

Answer 6

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.