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

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 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