Booleans operators behavior

Question

Edit: There was an error in my queries after this selection. Leaving here because the answer is interesting regardless.

I want to create a filter for MS SQL Server like:

WHERE ((this IS NOT NULL) or (that IS NOT NULL))

I tried with the replies given at a similar question:

filter((TAB1.c.this.isnot(None)) | (TAB2.c.that.isnot(None)))

filter(or_(TAB1.c.PERSONNE_ID.isnot(None), (TAB2.c.PERSONNE_ID.isnot(None)))

but with those options I can only get:

WHERE (this IS NOT NULL or that IS NOT NULL)

and in T-SQL this is a different filter from the one I want to obtain.

Any hints?

In what way are the WHERE statements different? Aren't the inner `()` in the first snippet simply not needed thanks to the operator precedence? — luk2302, Nov 22 '21 at 11:26
Not sure. Indeed it's also strange to my eyes, but if I test them in SQL queries they give different results — Vincenzo Lavorini, Nov 22 '21 at 11:38

luk2302 · Accepted Answer · 2021-12-30T11:04:46.280

The two WHERE clauses are identical due to IS NOT NULL having a higher operator precedence than OR and therefore being evaluated first before the OR gets evaluated.

See http://sqlfiddle.com/#!18/c0e9d/7

CREATE TABLE test (
  id INT,
  this INT,
  that INT
);
INSERT INTO test (id, this, that) VALUES (1, 2, NULL);
INSERT INTO test (id, this, that) VALUES (2, NULL, NULL);
INSERT INTO test (id, this, that) VALUES (3, NULL, 4);
INSERT INTO test (id, this, that) VALUES (4, 5, 6);

resulting in

SELECT * FROM test WHERE ((this IS NOT NULL) or (that IS NOT NULL));
SELECT * FROM test WHERE (this IS NOT NULL) or (that IS NOT NULL);
SELECT * FROM test WHERE (this IS NOT NULL or that IS NOT NULL);
SELECT * FROM test WHERE this IS NOT NULL or that IS NOT NULL;

all returning the rows 1, 3 and 4, omitting only item 2 which has both values as NULL.

Booleans operators behavior

1 Answers1