Not equals when using CASE statement in SQL
17,730
Solution 1
You could always just be more explicit with your case statement. Here's an example...
select
case when v1 = v2 then 1
when v1 <> v2 then 2
end
from test
Solution 2
First, you start by reading the documentation. You'll note that the SQL case
function takes one of two forms:
case {expression}
when {value-1} then {result-1}
...
when {value-N} then {result-N}
[ else {default-result} ]
end
or
case
when {boolean-condition-1} then {result-1}
...
when {boolean-condition-N} then {result-N}
[ else {default-result]
end
So, you can say something like
select * ,
case
when v1 = v2 then 1
when v1 != v2 then 2
when v1 is null and v2 is not null then 2
when v1 is not null and v2 is null then 2
else 1 -- both v1 and v2 are null
end as are_equal
from test
Note that
- You cannot mix the two forms, and
- The
else
clause is optional: ifnot specified, the return value from the function for any values not matching awhen
clause isnull
, nad - Because
null
fails all tests save for explicit tests for nullity viais [not] null
, if you need to check fornull
, you either have to use the 2nd form (... case when x is null then y else z end
) or have the null fall through and be handled by anelse
clause.
Author by
Trexion Kameha
Updated on June 04, 2022Comments
-
Trexion Kameha almost 2 years
In postgresql, I have a
case
statement that I need to add a "not equals" clause.When
v1
equals v2, I want it to say 1, whenv1
DOES NOT EQUALv2
, I would like to say 2.create table test ( v1 varchar(20), v2 varchar(20) ); insert into test values ('Albert','Al'),('Ben','Ben') select case v1 when v2 then 1 else 3 end from test
I tried using
!=
or<>
, but that does not seem to work.Does anyone have any idea how to use not equals in a
case
statement in SQL?