Reputation: 4938

NULL comparison, take 2

I have a subquery, used in WHERE section:

A.column <> B.column

Unfortunately, it doesn't work, if either A.column or B.column is NULL. So, I converted it to:

((A.column <> B.column) OR ((A.column IS NULL) <> (B.column IS NULL)))

, presuming that "Table.column IS NULL" is boolean value and I can compare 2 boolean values. But...

Incorrect syntax near '<'.

I don't like

((A.column <> B.column) OR ((A.column IS NULL) AND (B.column IS NOT NULL)) OR
((A.column IS NOT NULL) AND (B.column IS NULL)))

How could I workarounf this?

Regards,

Upvotes: 0

Answers (4)

Reputation: 76567

In MySQL you can use

WHERE NOT(A <=> B)

To do a WHERE A <> B that also works on null.

Upvotes: 0

Reputation: 129792

NULLIF yields null when two values are equal =)

WHERE NULLIF(A.column, B.column) IS NOT NULL

Upvotes: 3

Reputation: 73122

(ISNULL(A.column,0)) <> (ISNULL(B.column,0))

Upvotes: 1

Reputation: 1955

Use ISNULL function.

Upvotes: 3