Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)
share|improve this question
add comment

migrated from programmers.stackexchange.com Aug 22 '11 at 14:06

This question came from our site for professional programmers interested in conceptual questions about software development.

5 Answers

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)
share|improve this answer
add comment
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)
share|improve this answer
    
I can do following but performance is bad.. select * from main_set ms where (exists (select null from user_input ui where ui.id = ms.id) or not exists (select null from user_input ui)) –  Sree Aug 22 '11 at 14:32
    
@Sree: has anyone posted a solution with OR? Both Jack and I used UNION ALL: have you tried these? –  gbn Aug 22 '11 at 14:36
    
@Sree I think you can be neater than that - post edited with example –  Jack Douglas Aug 22 '11 at 14:37
    
@Jack: I am sorry..I could not format the query.. when I hit enter it just posting the comment not going to next line.. –  Sree Aug 22 '11 at 14:42
    
@gbn: that was actually my query..yes I tried union all..I was just pointing that not exists killing the performance..union all query performance is better than my query(with OR), I was wondering if there is better solution..otherwise I will accept your solution. –  Sree Aug 22 '11 at 14:45
add comment

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)
share|improve this answer
    
main_Set contain around 200000 records..not exists clause killing performance since there is no join...is there better way to maximize the performance.. –  Sree Aug 22 '11 at 14:34
1  
@Sree how do you know the not exists is killing performance? I very much doubt that it is. As you seem to be using Oracle - perhaps you should post your explain plan with cost/cardinalities –  Jack Douglas Aug 22 '11 at 14:48
add comment
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
      )
share|improve this answer
add comment

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

share|improve this answer
3  
Can you add some code to show what you mean please? –  gbn Aug 22 '11 at 14:24
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.