Maybe this looks pretty basic but i cannot explain the following behavior in Snowflake.

I got the following table containing 2 rows.

CREATE OR REPLACE TABLE foo (id integer, field1 string, field2 string);
INSERT INTO foo VALUES
  (23493132, 'Stop', 'Unexpected Downtime'),
  (23493132, 'Stop', 'Break')
;

Which give me the following table content

I want to select the row which got id 23493132 and where a field1 Stop is not associated with a field2 Break

I'm expecting a one-row result, the first one.

So i wrote the following query

SELECT 
    * 
FROM foo
WHERE (id = 23493132)
    AND (field1 != 'Stop' AND field2 != 'Break')
;

Nothing returned

On the other hand the following query using NOT and equality is giving me the expected result

SELECT
*
FROM foo
WHERE id = 23493132
    AND NOT (field1 = 'Stop' AND field2 = 'Break')
;

Can someone explain to me what is wrong with my first query as i'm a bit confused ...

CodePudding user response：

On the other hand the following query using NOT and equality is giving me the expected result

AND NOT (field1 = 'Stop' AND field2 = 'Break')

The equivalent query(*):

SELECT    * 
FROM foo
WHERE (id = 23493132)
    AND (field1 != 'Stop' OR field2 != 'Break');

(*) Assuming that field1/field2 are not nullable.

De Morgan's Law:

the negation of a conjunction is the disjunction of the negations

NOT (A AND B) <=> (NOT A) OR (NOT B)

CodePudding user response：

Just to expand on what @lukasz said:

What you seem to want to do is have the opposite of the following clause:

(field1 = 'Stop' AND field2 != 'Break')

the opposite of a clause (A and B) is actually (!A or !B)

This is because and narrows things downwards whereas or expands things outwards. So what you want is:

(field1 != 'Stop' OR field2 != 'Break')