CODEWITHSUNDEEP
sqloracle-databaseoracle11goracle10g
NightHawk
NightHawk

Reputation: 85

Oracle: Id Not in another table

I have following two tables, airport and flying

create table airport(airport_id number,flying_id,flying_company);

create table flying(flying_id,flying_company);

select airport_id 
from airport 
where flying_id not in(select flying_id from flying);

I would like to get airport_id where flying_id present in flying table but not present in airport table. I rote the following query using nested select query. Is there anyway I can improve without writing nested?

select airport_id 
from airport 
where flying_id not in (select flying_id from flying);

Upvotes: 0

Views: 1078

Answers (2)

APC
APC

Reputation: 146289

Do you have an actual problem with the query performance? Or do you just think it must be performing badly because you're selecting from FLYING?

Look at it this way: the database needs to read FLYING to get the set of FLYING_ID. Now if FLYING_ID is indexed (say if it is the primary key of FLYING) Oracle won't touch the table because the index is smaller. The index is the most efficient path there is. If FLYING_ID is not indexed then the database will have to read the whole table and extract the set of IDs, which is obviously less efficient but that's the minimum set of work which needs to be done.

So, should you consider switching to NOT EXISTS or LEFT OUTER JOIN as Tejash suggests?. Definitely you should if FLYING.FLYING_ID can be null. NOT IN is a trivalent test, and will return no rows if the subquery returns a result set which contains null. Quick demo on db<>fiddle.

Note that this is a matter of logic not performance. From a performance perspective it's likely that Oracle will use the same execution plan for NOT EXISTS, NOT IN and LEFT JOIN variants.

Upvotes: 1

Popeye
Popeye

Reputation: 35920

There are two ways.

  • LEFT JOIN
    SELECT DISTINCT F.*
      FROM FLYING F 
    LEFT JOIN AIRPORT A
        ON F.FLYING_ID = A.FLYING_ID
    WHERE A.AIRPORT_ID IS NULL;
  • NOT EXISTS
SELECT F.*
  FROM FLYING F
 WHERE NOT EXISTS 
       (SELECT 1 FROM AIRPORT A
         WHERE A.FLYING_ID = F.FLYING_ID);

Upvotes: 2

Related Questions