Oracle SQL Subquery - Usage of NOT EXISTS

Question

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.

Gordon Linoff · Accepted Answer · 2020-11-19 13:36:50Z

1

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)
                  )

answered Nov 19, 2020 at 13:36

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Marco Over a year ago

If the Table B has a couple of million records and first part of the query barely returns 10 records- Does the approach change. Is there a better option? Just curious

Gordon Linoff Over a year ago

@Marco . . . It seems strange to me that you are only comparing the minimum id to b, but that is what you are asking for. But not exists should be fine assuming you have an index on the column in b.

Marco Over a year ago

Some data corruption happened. We are trying to figure out the missing data. It does not have to be min. But random would have worked too. like how you had suggested in link. I just need the foreign key to be identified- Where primary Key is missing when compared to Table B.

Collectives™ on Stack Overflow

Oracle SQL Subquery - Usage of NOT EXISTS

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related