Merging exists and not exists into one query - oracle magic

Question

Hi,

In a WHERE part of query we have

SELECT * FROM SomeTable st
WHERE 
NOT EXISTS 
  (SELECT 1 FROM Tab t1 WHERE t1.A = st.A OR t1.B = st.A)
OR EXISTS 
  (SELECT 1 FROM Tab t2 WHERE (t2.A = st.A OR t2.B = st.A) AND t2.C IS NULL)

Seems like a good candidate for merging... But I'm staring on that for an hour without any idea.

Would you have some thoughts?

Thanks,

Answer 1

Try:

NOT EXISTS (SELECT 1 FROM Tab WHERE (Tab.A = @Id OR Tab.B = @ID)
            AND (Tab.C is null OR Tab.C != @Var))

Answer 2

I think I've got it

SELECT * FROM SomeTable st
WHERE 
0 = (SELECT SUM (NVL(t1.C,0) FROM Tab t1 WHERE t1.A = st.A OR t1.B = st.A)

What do you think? Of course that will work only for case where 'C' is expected to be NULL. If we are expecting some value, I think that the check

@SomeValue IN (SELECT t1.C FROM Tab t1 WHERE t1.A = st.A OR t1.B = st.A)

Will do?

Answer 3

It looks as though you're trying to return all SomeTable values where there's no corresponding value on Tab where C is not null - in which case, the following should work:

SELECT * FROM SomeTable st
WHERE NOT EXISTS 
  (SELECT 1 FROM Tab t1 WHERE (t1.A = st.A OR t1.B = st.A) AND t2.C IS NOT NULL)

However, that's not exactly what your existing query does - in your query, if there are two corresponding records on Tab, where one record has a non-null value for C and the other is null, your query will return a corresponding row, but my query won't.

Answer 4

SELECT distinct st.* 
FROM SomeTable st 
     left outer join Tab t
     on st.a in (t.a,t.b)
WHERE t.c is null

Sometimes the simplest answer is to use a join rather than an exists.