IN NOT IN SQL Server 2005

Question

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 will return NY,NJ,CT ect.... I just want to see what is in the ( )

Answer 1

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

Answer 2

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')

Answer 3

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

Answer 4

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