How to fallback to a different value in 'WHERE' clause sql?

I have a case where i have to fetch records for column field1='value1' if there are no values for 'value1' then i should fetch the record for 'default'.

For the above scenario I have used two queries:

`Select * from table_name where field1="value1"`

If the above query does not give back any record I fire the following query:

`Select * from table_name where field1="default"`

Now I want to do the above stated in one query. Can someone please help me with the same. I believe the answer lies somewhere in using `CASE WHEN` clause.

Also the above queries should work for oracle, postgres as well as mysql.

For Mysql:

SET @a = Select Count(*) from table_name where field1="value1"
IF @a > 0 Then
Select
*
from
table_name
where
field1="value1"
ELSE
Select
*
from
table_name
where
field1="default"
END IF

Core ANSI SQL answer, expected to run on all different platforms:

select * from table_name
where field1 = 'value1'
or (field1 = 'default'
and NOT EXISTS (select 1 from table_name where field1 = 'value1'))

Okay , i tried out this it seems to be working .

SELECT * FROM table_name WHERE
CASE
WHEN EXISTS(SELECT * FROM table_name WHERE field1='value1') THEN field1= 'value1'
ELSE field1='default'
END

You could try this...

select * table_name where field1="default"
and not exists (select * from table_name where field1="value1")
union all
select * from table_name where field1="value1"

Use CASE and Exists like below

Select * from table_name where field1=
case when exists(select 1 from table_name where field1='value1')
then 'value1' else 'default 'end

You can use `case` statement with `count` check.

select *
from table_name
where coulmn_1 = (case
when (select count(1) from dc_date_card where coulmn_1 = value_1) > 0 then
value_1
else
Default
end)

Optimal solution using `coalesce()`:

Select * from table_name where field1 = coalesce (
(select field1 from table_name where field1='value1' limit 1)
, 'default'
);

Notice the `limit 1` in the subquery: In this case it is mandatory to ensure that subselect doesn't return more than one row. But even using the `case when exists (...)` approach it is a good practice to add it because, otherwise, database engine would be forced to scan all rows matching the subquery.

Of course, most modern databases are smart enough to silently optimize it. But some old ones could not. And anyway, it can be cases where they cant.

That is, for example, in PostgreSQL, if subquery uses non (or non properly declared as) stable functions, the planner would be forced to perform a full scan to produce consistent results if that function has any side effect.