CODEWITHSUNDEEP
sqlpostgresqlboolean-expression
user66081
user66081

Reputation: 458

Boolean logic in SQL SELECT

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?

Upvotes: 2

Views: 549

Answers (2)

Fr0zen
Fr0zen

Reputation: 31

You can write 

SELECT CASE
       WHEN (a IS NULL) OR (b IS NULL) THEN NULL
       WHEN (a = b) THEN 'true'
       ELSE 'false'
       END

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269693

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.

Upvotes: 5

Related Questions