Joining tables using SQL

What I am trying to do is that I have two diff开发者_运维知识库erent tables and joining them by a column. The result im looking for is retrieving all of the records that are NOT in the in the first table. I cant remember the sytax to do this. Here is what I have so far:

        SELECT A.ICAO, A.IATA, A.AIRPORT_NAME, A.CITY, A.COUNTRY, A.REVISED_DATE 
        FROM AIRPORT_CHECKLIST A, AIRPORT_CHECKLIST_SELECTED B
        WHERE A.COMPANY = 'TOM'
        AND A.ICAO <> B.ICAO

could try

    SELECT A.ICAO, A.IATA, A.AIRPORT_NAME, A.CITY, A.COUNTRY, A.REVISED_DATE 
    FROM AIRPORT_CHECKLIST A
    WHERE A.COMPANY = 'TOM'
    AND A.ICAO NOT IN(SELECT ICAO FROM AIRPORT_CHECKLIST_SELECTED)

or the other way if A is the "first" table..

    SELECT A.ICAO, A.IATA, A.AIRPORT_NAME, A.CITY, A.COUNTRY, A.REVISED_DATE 
    FROM AIRPORT_CHECKLIST A, AIRPORT_CHECKLIST_SELECTED B
    WHERE A.COMPANY = 'TOM'
    AND B.ICAO NOT IN(SELECT ICAO FROM AIRPORT_CHECKLIST)

To find all records without a match, do a left inner join (which returns one record for each record in the left-hand table, even if it doesn't have a match in the right-hand table) and check for null values in the right-hand table's fields. Assuming you're looking for airports in the checklist for 'TOM' that are not selected:

SELECT A.ICAO, A.IATA, A.AIRPORT_NAME, A.CITY, A.COUNTRY, A.REVISED_DATE
FROM AIRPORT_CHECKLIST A LEFT JOIN AIRPORT_CHECKLIST_SELECTED B 
ON A.ICAO = B.ICAO
WHERE A.COMPANY = 'TOM' AND B.ICAO IS NULL;

The test B.ICAO IS NULL selects only those rows that have been inserted by the inner join to represent A rows with no matching B row

You'll need to use a right outer join and check for nulls.

select 
    A.ICAO, 
    A.IATA, 
    A.AIRPORT_NAME, 
    A.CITY, 
    A.COUNTRY, 
    A.REVISED_DATE
from 
    airport_checklist a
right outer join 
    airport_checklist_selected b
on 
    a.icao = b.icao
where 
    a.icao is null

Of course, as djacobson's comment points out, since this returns only table A where table A is empty, it will return a set of nulls!