EXISTS clause in SQL

Question

I have following requirement: main_set table has all the ids, user_input table has all user entered ids.

I want result of ids that exist in user_input table. If nothing in user_input table then I want all the ids from main_input table.

Here's what I have so far:

create table main_set as
select 1 id from dual
union all
select 2 from dual
union all
select 3 from dual

create table user_input as
select 1 id from dual
union all
select 2 from dual

select *
  from main_set ms
  where exists (select null
                        from user_input ui
                       where ui.id = ms.id)

Answer 1

Try this query. It will get a count of records that exist in both main_set and user_input. Then, it'll use that count to determine whether to return all main_set records (when c.c = 0) or only those that match (when c.c > 0 and ui.id is not null)

select ms.id
  from main_set ms
  left join user_input ui on ms.id = ui.id
  cross join (
    select count(*) c 
    from main_set ms
    left join user_input ui on ms.id = ui.id
    where ui.id is not null) c
where c.c = 0 or (c.c > 0 and ui.id is not null)

Answer 2

select id from user_input
union all
select id from main_set where not exists(select * from user_input);

--edit based on your comment

select *
from main_set
where id in(select id from user_input) or not exists(select * from user_input)

or, seeing as you mention performance, perhaps you should try a join as an alternative:

select main_set.*
from main_set left outer join user_input on(user_input.id=main_set.id)
where user_input.id is not null or not exists(select * from user_input)

Answer 3

You have 2 different queries so I'd probably use a UNION

select *
  from main_set ms
      --check for *matching rows* in user_input
  where exists (select *
                        from user_input ui
                       where ui.id = ms.id)
UNION ALL
select *
  from main_set ms
      --check for *no rows* in user_input
  where not exists (select * from user_input)

Answer 4

select * 
from main_set ms 
where 
      (select count(*) from user_input) = 0
   or 
      exists (
         select 1
         from user_input ui
         where ui.id = ms.id
      )

Answer 5

Using a case statement should do the job instead of an exists clause.