IN NOT IN SQL Server 2005

How can I see what is not in the table... I know I know...can only see what is there but come on!!!

So!!

select * from ORDER where State IN ('MA','PA','GA','NC')       

So I will get MA and PA but I want to see GA and NC....

NOT IN w开发者_运维技巧ill return NY,NJ,CT ect.... I just want to see what is in the ( )

It looks like you are missing a single quote ' in front of GA.

My understanding of the question is: For a given list of states, which ones do not exist in the Order table?

This will show you what states out of the four listed below have no corresponding records in the Order table:

select distinct s.State
from
(
    select 'MA' as State
    union all
    select 'PA'
    union all
    select 'GA'
    union all
    select 'NC'
) s
left outer join [Order] o on s.State = o.State
where o.State is null

I'm going to try to read between the lines a little here:

;with cteStates as (
    select 'MA' as state
    union all
    select 'PA'
    union all 
    select 'GA'
    union all
    select 'NC'
)
select s.state, count(o.state) as OrderCount
    from cteStates s
        left join [order] o
            on s.state = o.state
    group by s.state

Are you just trying to find out which states there are except for those four? If so:

SELECT DISTINCT State FROM dbo.ORDER WHERE State NOT IN ('MA', 'PA', 'GA', 'NC')