Search code examples
sqloracle-databasesubquerynot-exists

Oracle SQL Subquery - Usage of NOT EXISTS

I used a query to find a list of Primary Keys. One Primary key per each ForiegnKey in a table by using below query.

select foreignKey, min(primaryKey)
from t
group by foreignKey;

Let us say this is the result : 1,4,5

NOw I have another table - Table B that has list of all Primary keys. It has 1,2,3,6,7,8,9

I want a write a query using the above query So that I get a subset of the original query(above) that does not exist in Table B. I want 4 and 5 back with the new query.

Solution

  • Use a having clause:

    select foreignKey, min(primaryKey)
from t
group by foreignKey
having min(primarykey) not in (select pk from b);

    You should also be able to express this as not exists:

    having not exists (select 1
                   from b
                   where b.pk = min(t.primaryKey)
                  )