I have following two tables, airport and flying
x
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);
Advertisement
Answer
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);