Funny problem with table selection

[sonaliak]

select * from mwebrepseloptions where repopt_required not in (10, 20) ....... returns 0
select * from mwebrepseloptions where repopt_required is not null ....returns 139 (correct)
select * from mwebrepseloptions where repopt_required is null ....returns 8 (correct)
select * from mwebrepseloptions where repopt_required <>10 or repopt_required <>20 ....returns 139(wrong)
select * from mwebrepseloptions where repopt_required =10 or repopt_required =20....returns 139(might be correct)
select count(*) from mwebrepseloptions... returns total 147(correct)

Repopt_required Column is defined as number.


My problem is why
select * from mwebrepseloptions where repopt_required <>10 or repopt_required <>20 ....returns 139(wrong)
select * from mwebrepseloptions where repopt_required =10 or repopt_required =20....returns 139(might be correct)


these 2 queries return same number of rows(rather same rows) when <> should return 8 rows and not 139 rows ???


thanks

[chrisrlong]

The query is writtin incorrectly

You want to say:

select * from mwebrepseloptions where repopt_required <>10 AND repopt_required <>20

Otherwise, you get all non-null records, because those that are 10 are NOT 20 and those that are 20 are NOT 10, so they all satisfy the query.


Note that NULLS ALWAYS return false for = > < *or* <>

The only way to find nulls is with IS NULL.

HTH

- Chris

[sonaliak]

thanks a lot Chris