SQL: IF/CASE statement within WHERE clause

Question

Is it possible to return records matching the first part of a where clause; however, if no results are found, then move to the second part of the where clause?

Example:

create table #Fruits (
    Fruit varchar(20),
    Color varchar(20),
    Size varchar(20)
)

insert into #Fruits
values ('Apple', 'Red', 'Medium'),
       ('Pear', 'Green', 'Medium'),
       ('Banana', 'Yellow', 'Medium'),
       ('Grapes', 'Purple', 'Small')

select * from #Fruits
where Fruit in ('Apple', 'Grapes') or Color = 'Green'

This will obviously return Apple, Grapes and Pear. My goal is to only find Apple and Grapes if they exist, otherwise return the Fruits that are Green.

I've tried to refer to this similar question: SQL: IF clause within WHERE clause but am having trouble incorporating the where.

I've also tried using @@rowcount:

select * from #Fruits where Fruit in ('Apple', 'Grapes')
if @@rowcount = 0
select * from #Fruits where Color = 'Green'

But if the first select returns nothing, it still returns an empty table as a result.

Thanks.

Answer 1

We can express your logic using a union:

select * from #Fruits where Fruit in ('Apple', 'Grapes')
union all
select * from #Fruits where Color = 'Green' and
                            not exists (select 1 from #Fruits
                                        where Fruit in ('Apple', 'Grapes'));

We might also be able to combine the logic into a single query:

select *
from #Fruits
where
    Fruit in ('Apple', 'Grapes') or
    (Color = 'Green' and
     not exists (select 1 from #Fruits where Fruit in ('Apple', 'Grapes'));