how to search for something not in and null

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

how to search for something not in and null

Author

Topic

csphard
Posting Yak Master

113 Posts

Posted - 2005-01-26 : 13:04:07

The following statement works, my problem is that some e.bureau's are
null. How do i search for bureaus not in (this) and include bureaus that are null.

select d.eval_type,e.bureau,e.deptno,d.rating
from due_evals d, emp_information_test e
where d.todate between '01/01/2004' and '06/30/2004'
and e.empid = d.empid
and e.bureau not in ('1074','375','385','725','726','727')
order by e.bureau

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-26 : 13:09:01

and (e.bureau not in ('1074','375','385','725','726','727')
or e.bureau is null)

Go with the flow & have fun! Else fight the flow

SamC
White Water Yakist

3467 Posts

Posted - 2005-01-26 : 13:47:25

Another solution for variety

and IsNull(e.bureau, 0) NOT IN ('1074','375','385','725','726','727')

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-26 : 13:49:51

sam, doesn't that disable the indexes on the column??

Go with the flow & have fun! Else fight the flow

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-01-26 : 14:21:10

'OR' isn't too good for preventing table scans either...
I usually keep a parse function handy to act as my @exclude table
but when I want efficient execution plans with large tables I
try to avoid NOT INs at most costs with something like this:


declare @exclude table (bureau varchar(12))
insert @exclude
Select	'1074' union
Select	'375' union
Select	'385' union
Select	'725' union
select  '726' union
select	'727'

select	d.eval_type
	,e.bureau
	,e.deptno
	,d.rating
from	due_evals d
JOIN	emp_information_test e 
		ON e.empid = d.empid 
Left JOIN @Exclude x
		ON e.bureau = x.bureau
where	d.todate between '01/01/2004' and '06/30/2004' 
and	x.bureau is NULL
order by e.bureau

SamC
White Water Yakist

3467 Posts

Posted - 2005-01-26 : 15:13:11

So... NOT IN is fine as long as NULLs are not an issue? <hopeful look>

SamC
White Water Yakist

3467 Posts

Posted - 2005-01-26 : 15:19:00

Nigel -

I've often wondered if it improves performance if an index were applied to a temporary table like the one in your example?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-01-26 : 15:40:44

SamC, I can't think of a case (off the top of my head) where I would prefer a NOT IN to an outer join. I believe the statement I did (above) will satisfy the "bureau NOT IN" and the "or bureau is Null" requirements. If one of you gurus out there disagrees with OUTER JOIN vs NOT IN, please set me straight.

Nigel, I this case I think the optimizer will ignore any indexes anyway because it needs to look at every record. Which is fine because there's only a handfull of records in there anyway.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-01-26 : 15:45:09

Sorry Sam, I just realized that was you directing your index question to Nigel.

SamC
White Water Yakist

3467 Posts

Posted - 2005-01-26 : 15:46:50

I don't really mind anyone addressing the point.

What I doubt is whether there is any execution plan differences using NOT IN versus an OUTER JOIN.

(Unless testing for NULLs is involved)

X002548
Not Just a Number

15586 Posts

Posted - 2005-01-26 : 15:53:57

Where did Nigel go?

Brett

8-)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-01-26 : 16:14:12

I'm not sure who this Nigel guy is and why I was talking to him but anyway...

After doing some testing, for the above example I think I was totally wrong.
(nothing new for me). For a large table with just a handfull of values in a NOT IN
compared to those same values in an outer join situation, the NOT IN outperformed the
outer join. However, in a situation where you may want to find all the records that
are in one (large) table that are NOT IN another (large) table the outer join is the way to go.

I'm not sure how to know when to use which. Maybe Nigel can tell us.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-26 : 16:34:51

TG, nigel's nick is "nr" and is a MVP of SQL server. i remember there was once a disscusion which is better:
not in, not exists and left join. for small tables i think the results were:
1. not exists
2. not in
3. left join.

Go with the flow & have fun! Else fight the flow

csphard
Posting Yak Master

113 Posts

Posted - 2005-01-26 : 16:55:32

I want to thank you guys for all your help. I am trying the different
scenarios right now.

I tried the following and It worked.

and IsNull(e.bureau, 0) NOT IN ('1074','375','385','725','726','727')

I do not understand the following yet but i am working on it.

select d.eval_type
,e.bureau
,e.deptno
,d.rating
from due_evals d
JOIN emp_information_test e
ON e.empid = d.empid
Left JOIN @Exclude x
ON e.bureau = x.bureau
where d.todate between '01/01/2004' and '06/30/2004'
and x.bureau is NULL
order by e.bureau

Thanks again

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-01-26 : 18:01:37

Thanks, Spirit1 (ExternalPerson1), I guess I'm going to need a new function:
dbo.fnDontDoWhat('TG Says')

SamC
White Water Yakist

3467 Posts

Posted - 2005-01-27 : 11:12:55

quote:
Originally posted by TG

Thanks, Spirit1 (ExternalPerson1), I guess I'm going to need a new function:
dbo.fnDontDoWhat('TG Says')

I think you're being a little hard on yourself there, particularly so because your solution is the better of the two. Maybe we need to ask Graz (the site administrator) to open a forum for SQLTeam therapy.

We have a Dr. here. His name is Join, Dr. Cross Join.

I'm sure he can help.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-01-27 : 16:26:12

LOL !
When it comes to therapy, SQLTeam or otherwise, I could use all I can get.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources