Embedded SQL Query issue

[gb]

This is kind of discrimination query:

select distinct a.state_code from MyTable a
where a.state_code not in (
select distinct b.state_code from MyTable b
where b.report_date = CONVERT(char(10), GETDATE(), 126))

The external query:
select distinct a.state_code from MyTable a
returns the dataset:

state_code
CO
FL
IA
IL
KS
MN
MO
OH
TN
WI

The internal query:
select distinct b.state_code from MyTable b
where b.report_date = CONVERT(char(10), GETDATE(), 126)

returns:

state_code
CO
IA
IL
KS
MN
MO
OH
TN
WI

So I am expecting to get from original query the result:

state_code
FL

But it is to get stuck...

At the same time the query:

select distinct a.state_code from MyTable a
where a.state_code not in (
'CO',
'IA',
'IL',
'KS',
'MN',
'MO',
'OH',
'TN',
'WI'

)

returns the result very fast:
state_code
FL

Please help me to speed up the original query.


Thanks,
GB

[Erland Sommarskog]

Post by gb
select distinct a.state_code from MyTable a
where a.state_code not in (
select distinct b.state_code from MyTable b
where b.report_date = CONVERT(char(10), GETDATE(), 126))

I don't know what is going on, but I always prefer NOT EXISTS for this
type of queries:

SELECT DISTINCT a.state_code
FROM MyTable a
WHERE NOT EXISTS (SELECT *
FROM MyTable b
WHERE a.StateCode = b.StateCode
AND b.report_date = convert(char(8), GETDATE(), 112))

I also changed the format code to 112, that is YYYYMMDD, since this format
is always interpreted in the one and same way.