sql multiple conditions together not true

Question

My question is very simple but I cannot get my head around

my statement:

SELECT * 
FROM table 1 
WHERE (row1 = 0 AND row2 > 0) 
   OR (row3 is NULL AND row4 > 0)

This gives me a selection of rows that is correct for one option.

What I want is making a selection of all rows where the where clause is not true in total

SELECT * 
FROM  table 1
WHERE NOT((row1 = 0 AND row2 > 0) 
           OR (row3 is NULL AND row4 > 0))

what is wrong is

SELECT * 
FROM table 1 
WHERE (row1 > 0 AND row2 = 0) 
   OR (row3 is NOT NULL AND row4 = 0)

Answer 1

I think this is what you are looking for? I've assumed you meant fields rather than rows though...

SELECT *
FROM table1
WHERE not exists

(select *
from table1
 where
 ((field1 = 0 and field2 > 0) OR (field3 IS NULL AND field4 > 0)))

Maybe see this for further information on NOT IN AND NOT EXISTS to exclude results based on your subquery specification:

NOT IN vs NOT EXISTS

Answer 2

I suspect your problem is to do with NULL values in columns, because any predicate with NULL evaluates to NULL, so is neither true or false:

Imagine a simple table T with one column (Col):

Col
-----
1
2
3
NULL

So if we run:

SELECT  Col
FROM    T
WHERE   Col = 1;

We get one row, where Col = 1, therefore logically if you run:

SELECT  Col
FROM    T
WHERE   NOT (Col = 1);

Or

SELECT  Col
FROM    T
WHERE   Col <> 1

You would expect the remaining 3 rows, but in both cases you will only get:

Col
----
2
3

This is because NOT(NULL = 1) and NULL <> 1 are both NULL, so not true, so the 4th row isn't returned.

The way to get the result you are after is to use EXCEPT

SELECT  Col
FROM    T
EXCEPT
SELECT  Col
FROM    T
WHERE   Col = 1

Examples on SQL Fiddle

Answer 3

If you want to take NULLs into account, try this:

select * from table1 
where Not((row1 = 0 and row2 > 0) or  (row3 is null and row4 > 0))
or (row1 is null or row2 is null) and row4 is null