CODEWITHSUNDEEP
oracle-databaseoracle11g
Zaya
Zaya

Reputation: 336

How can I check if one subquery is a subset of another subquery in Oracle SQL?

Is it possible to create a query in Oracle SQL that selects only elements such that a subquery is a subset of another subquery?

For example:

select Foo.f
from Foo
where

    --The set of all Bar.b's where Foo.f=Bar.b
    (select Bar.b
    from Bar
    where Foo.f = Bar.b)

    contains 

    --The set of all Bar.b's where Bar.c=10
    (select Bar.b
    from Bar
    where Bar.c=10);

Or "select all Foo.f where, all Bar.b with Bar.c=10 is a subset of all Bar.b where Foo.f=Bar.b"?

[EDIT] So here may be a better way of phrasing what I'm asking. If there are two subqueries in a where statement which return subsets A and B, how can I check that B is a subset of A. So to rewrite the initial example in a more generic way:

Select Foo.f
from Foo
where
    --Subset A
    (select ...)

    ?? Some set comparison operator

    --Subset B
    (select ...)

Upvotes: 2

Views: 690

Answers (3)

Zaya
Zaya

Reputation: 336

So, I believe I found an answer. First, note that if B ⊆ A then B-A = ∅

Then, using only SQL set operators, I can do

select Foo.f
from Foo
where
    not exists (
        --Subset B
        (select...)

        minus

        --Subset A
        (select...));

Upvotes: 2

Kaushik Nayak
Kaushik Nayak

Reputation: 31676

I think a simple AND within a EXISTS query should work.

select foo.f
from Foo  where exists ( select 1 from Bar  where Foo.f = Bar.b and Bar.c = 10 )

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 143023

Huh, you wrote quite a lot of code for something that I interpreted as simple

select Foo.f
from Foo join Bar on Foo.f = Bar.b
where Bar.c = 10

Upvotes: 0

Related Questions