select not (value) in returno 0 row

Question

Post reply

select not (value) in returno 0 row

Sergio Campos

SSC-Addicted

Points: 459
More actions
March 9, 2006 at 3:26 am

#113221

Hi i have a sql-query:
mprod have +- 150000 rows
nuevos_productos_ingram_temp +- 1600
this sql return 2 rows
select * from appmis.cambio_icg.dbo.nuevos_productos_ingram_temp
where partno in(select vendorref from intranet.dbo.mprod)
but
select * from appmis.cambio_icg.dbo.nuevos_productos_ingram_temp
where not partno in(select vendorref from intranet.dbo.mprod)
retun 0 rows instead 16xx
any help plz?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jesper-244176 SSCertifiable Points: 7082 More actions · Answer 1

That's probably because

select vendorref from intranet.dbo.mprod

returns (among other things) a null value

Try this instead:

select * from appmis.cambio_icg.dbo.nuevos_productos_ingram_temp

where not partno in(select vendorref from intranet.dbo.mprod where vendorref is not null)

paramind SSCrazy Points: 2140 More actions · Answer 2

in and not in are not simply the opposite of each other in case nulls occur.

why?

because for a specific partnumber, you can tell, that its is IN a selection if there are SOME positive numbers AMONG ANY NUMBER of nulls.

but if at least one null occurs in the NOT IN selction this is sufficient to deny an answer, since you can't tell, whether this NULL would evaluate to any of your partnumbers if it was defined.

_/_/_/ paramind _/_/_/

Sergio Campos SSC-Addicted Points: 459 More actions · Answer 3

ok, thanks, filtering null values thats work

Many thanks

Jesper-244176 SSCertifiable Points: 7082 More actions · Answer 4

I agree with paramind, but would like to elaborate a little further (to help myself understand it better ). Consider the following queries on the Northwind database:

select c.region from customers c where c.region in (null, 'SP')

select c.region from customers c where not c.region in (null, 'SP')

These queries can be rewritten as

select c.region from customers c where c.region = null or c.region = 'SP'

select c.region from customers c where not (c.region = null or c.region = 'SP')

Here c.region = null evaluates to unknown. c.region = 'SP' is true (for row rows where region is SP), unknown (for rows where region is NULL) and otherwise false. Following the rules for three-valued logic, the combined expression

c.region = null or c.region = 'SP'

is either true (for rows where region is SP) or unknown. Negating this using the not expression gives either false (for rows where region is SP) or unknown. Thus, the last query

select c.region from customers c where not (c.region = null or c.region = 'SP')

has a where clause that is either false or unknown and therefore returns no rows.

I guess "not region in..." is the same as "region not in..."

There is a very nice article by Michael Coles on NULL and three-valued logic at http://www.sqlservercentral.com/columnists/mcoles/fourrulesfornulls.asp, if you want to go deeper into it.