Oracle comparison issue

Question

I'm trying to compare two columns from different columns.

Ex:

Select a.Col1, b.Col1,
CASE
WHEN a.Col1 <> b.Col1 THEN 'TRUE'
ELSE 'FALSE'
END CASE
FROM TableA a LEFT OUTER JOIN TableB b ON a.id = b.id

I always get false but not true even though they are different or if there is a value in TableA and not in TableB.

What is wrong with my code?

Answer 1

Hi Rahuls,

your query seems correct:

SQL> WITH tableA AS (SELECT 1 ID, 'A' col1 FROM dual
  2                  UNION ALL SELECT 2 ID, 'B' FROM dual),
  3       tableB AS (SELECT 1 ID, 'B' col1 FROM dual)
  4  SELECT a.Col1, b.Col1,
  5         CASE
  6            WHEN a.Col1 <> b.Col1 THEN
  7             'TRUE'
  8            ELSE
  9             'FALSE'
 10         END CASE
 11    FROM TableA a
 12    LEFT OUTER JOIN TableB b ON a.id = b.id;

COL1 COL1 CASE
---- ---- -----
A    B    TRUE
B         FALSE

however as you can see if one of the element is NULL it will return FALSE (when a row from A doesn't exist in B for example). If you want to get TRUE when there is a NULL or no element in B just reverse the case:

SQL> WITH tableA AS (SELECT 1 ID, 'A' col1 FROM dual
  2                  UNION ALL SELECT 2 ID, 'B' FROM dual),
  3       tableB AS (SELECT 1 ID, 'B' col1 FROM dual)
  4  SELECT a.Col1, b.Col1,
  5         CASE
  6            WHEN a.Col1 = b.Col1 THEN
  7             'FALSE'
  8            ELSE
  9             'TRUE'
 10         END CASE
 11    FROM TableA a
 12    LEFT OUTER JOIN TableB b ON a.id = b.id;

COL1 COL1 CASE
---- ---- -----
A    B    TRUE
B         TRUE

This behaviour comes from the fact that if b is NULL then a <> b is neither TRUE nor FALSE, it is UNKNOWN

Update: regarding your comment -- if you want to add cases when a <> b you can do so in extra WHEN clauses:

SELECT a.Col1, b.Col1,
       CASE
          WHEN a.Col1 = b.Col1 THEN
           'FALSE'
          WHEN to_char(SYSDATE, 'D') = 7 THEN
           'S'
          ELSE
           'O'
       END CASE
  FROM TableA a
  LEFT OUTER JOIN TableB b ON a.id = b.id;