Admin

I want to compare two tables and display items from one table that are not in the other table. The query returns no results as written below. Thanks.

select distinct STATE_lookup.STATE, state_fullname from STEO.ADOPT_STATE, STEO.STATE_LOOKUP
where STATE_LOOKUP.STATE NOT IN (select STATE from adopt_state)
and state_lookup.state <> 'DC'
and state_lookup.state <> 'US'
order by state_fullname

Solution:

select distinct sl.STATE, sl.state_fullname
from STEO.STATE_LOOKUP sl
where sl.STATE NOT IN (select STATE from adopt_state)
and sl.state <> 'DC'
and sl.state <> 'US'
order by sl.state_fullname

and

SELECT DISTINCT
sl.state,
sl.state_fullname
FROM STEO.STATE_LOOKUP AS sl
WHERE
sl.state NOT IN (SELECT state FROM STEO.adopt_state) AND
sl.state <> 'DC' AND
sl.state <> 'US'
ORDER BY sl.state_fullname

or

select state, state_fullname from state_lookup l
where not exists
(select 1 from adopt_state a
where a.state = l.state)
and state <> 'DC'
and state <> 'US'
order by state_fullname;