Booleans operators behavior-CodePudding

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?

CodePudding user response：

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/2

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;

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