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

Question

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.

Answer 1

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

Answer 2

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