Subquery with NOT IN

Question

I have a city table like:

state  city
-----  ----
texas  houston
texas  austin
texas  dallas
texas  san antonio
texas  beaumont
texas  brownsville
texas  el paso
california  anaheim
california  san diego
california  los angeles
california  oakland
california  simi valley
california  san francisco

I need a query to find states that don’t have a city named Houston or Dallas. My first thought was this

select distinct state from cities where city not in ('houston', 'dallas');

but that will not work. I think I need a subquery and NOT IN of some kind.

Answer 1

The way you can do this is with a sentence NOT EXISTS:

Select  Distinct State
From    Cities  C1
Where   Not Exists
(
    Select  *
    From    Cities  C2
    Where   C2.City In ('Houston', 'Dallas')
    And     C1.State = C2.State
)

Answer 2

select distinct state from cities where state not in (SELECT state FROM cities WHERE city in ('houston', 'dallas'));

Answer 3

Another method may be slightly faster:

select distinct state from cities where state not in (select state from cities where city in ('houston', 'dallas'));

Answer 4

Select State
from Cities
group by State
having count(case when Cities in ('houston', 'dallas') then cities end) = 0

This will return all states in which the number of cities associated with this state and matching your criteria is 0 (i.e. there are no such cities associated with the state).