Is there a simpler way to implement
select (case when ((a is null) or (b is null)) then null else (case when
(a = b) then true else false end) end) from ...
in PostgreSQL, or other major RDBMS for that matter?
2 Answers
I think the simplest is:
select (a = b)
although this is not exactly your logic, because this returns NULL if either a or b are NULL. It might be good enough.
If you want it to return false if either a or b are NULL but not both, then:
select (a = b) and nullif(coalesce(a, b) is null, true)
EDIT:
Because you ask about other databases, one way of expressing the logic would be:
(case when a = b then 1 when a <> b then 0 end) as flag
This returns NULL if either is NULL.
5 Comments
user66081
thanks. my logic is wrong. I want null if either is null (will changed the question).
Gordon Linoff
@user66081 . . . That is the first option; it seemed like a reasonable interpretation.
user66081
For some reason I though it wouldn't work without trying. Do you know if this has a high chance of working on other major RDBMS?
Gordon Linoff
@user66081 . . . If the database supports a boolean type then it should work. Otherwise, you'll need more explicit logic.
You can write
SELECT CASE
WHEN (a IS NULL) OR (b IS NULL) THEN NULL
WHEN (a = b) THEN 'true'
ELSE 'false'
END
select a = bwill return exactly the same thing ora is not distinct from b