SQL Snippets ™: Tutorials for Oracle Developers

In SQL you should always consider the effect of null values when comparing two values for equality (or any type of comparison for that matter). Consider a table where two of its columns can contain null values.

select * from t;
 

 C1 C2         C3
--- ---------- ----------
  1 A          A
  2 A          B
  3 (null)     A
  4 (null)     (null)
 

If we attempt a SELECT statement like the following we will only get row 1.

select *
from   t
where  c2 = c3 ;
 

 C1 C2         C3
--- ---------- ----------
  1 A          A
 

Row 4 is not returned because, in SQL, a null is not considered to be equal to or unequal to any value (including another null). If this is the behaviour you need, then read no further. However, if you need a query that returns row 1 and row 4 then try one of the solutions in the subtopics to follow.