Not equals when using CASE statement in SQL

Question

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, when v1 DOES NOT EQUAL v2, 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?

Answer 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

Answer 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 a when clause is null, nad
• Because null fails all tests save for explicit tests for nullity via is [not] null, if you need to check for null, 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 an else clause.