How to select the comparison of two columns as one column in Oracle

Question

I cannot figure out how to add a column to my SELECT query indicating whether two columns contain the same data in Oracle.

I would like to write a query like:

select column1, column2, column1=column2 from table 

and, if I have this table:

+---------+---------+ | column1 | column2 | +---------+---------+ | value1  | value1  | | value2  | value3  | | value4  | value4  | +---------+---------+ 

get a result like:

+---------+---------+-----------------+ | column1 | column2 | column1=column2 | +---------+---------+-----------------+ | value1  | value1  | true            | | value2  | value3  | false           | | value4  | value4  | true            | +---------+---------+-----------------+ 

What is the correct syntax to do this?

Answer 1

If you want to consider null values equality too, try the following

select column1, column2,     case       when column1 is NULL and column2 is NULL then 'true'         when column1=column2 then 'true'        else 'false'     end  from table; 

Answer 2

I stopped using DECODE several years ago because it is non-portable. Also, it is less flexible and less readable than a CASE/WHEN.

However, there is one neat "trick" you can do with decode because of how it deals with NULL. In decode, NULL is equal to NULL. That can be exploited to tell whether two columns are different as below.

select a, b, decode(a, b, 'true', 'false') as same   from t;       A       B  SAME ------  ------  -----      1       1  true      1       0  false      1          false   null    null  true