CODEWITHSUNDEEP
sqlsqlite
Khantahr
Khantahr

Reputation: 8528

Query for missing items in a table

I have a table of airport code pairs:

|iata|icao|
-----------
|ORD |KORD|
|JFK |KJFK|
|LAX |KLAX|
|SFO |KSFO|

I want to run a query that will return the codes that do not exist in this table, so if I run a query (against the iata column) containing ATL,ORD,MIA,SFO it would return ATL and MIA since neither of those exist in my table. Is this possible?

Upvotes: 0

Views: 1190

Answers (3)

user2225399
user2225399

Reputation: 69

in oracle you could use minus command example

select your_columns from tableA minus select your_columns from tableB

note that both must have same columns and types and lengths or it will not work.

if type is different do cast, to_char, to_date or whatever function is necessary to get types the same. Note if you do have to do one of these or any oracle function call in where clause then use function based indexes. For example, if to_upper is called in where clause like this

select * from tableName a where to_upper(last_name)='SIMPSON'

an index on this would be created as follows

create index ixt on tableName(to_uppper(last_name));

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270081

If you have a query that runs code, here is one way:

    select mc.*
    from (<your query goes here>) mc
    where mc.iata not in (select iata from AirportCodePairs acp)

You can also do this with a left outer join and comparison to NULL. I would recommend an index on iata in the pairs table.

Or, if you don't have a query, you can do:

    select mc.*
    from (select 'ATL' as code union all
          select 'ORD' union all
          select 'MIA' union all
          select 'SFO'
         ) mc
    where mc.iata not in (select iata from AirportCodePairs acp)

Upvotes: 2

Marjeta
Marjeta

Reputation: 1141

You can create a table with the values you are searching for and then do a JOIN:

CREATE TABLE tmp ( code char(3) NOT NULL);

INSERT INTO tmp (code) VALUES ('ATL'), ('ORD'), ('MIA'), ('SFO');

SELECT code FROM tmp
LEFT OUTER JOIN  airportcode as ac ON tmp.code = ac.iata
WHERE ac.iata IS NULL

Upvotes: 0

Related Questions