Nulls and Equality

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.




Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

  • [url=http://www.sqlsnippets.com/en/topic-10565.html]SQL Snippets: Nulls - Nulls and Equality[/url]

To link to this page in HTML documents or Blogger comments cut and paste this code.

  • <a href="http://www.sqlsnippets.com/en/topic-10565.html">SQL Snippets: Nulls - Nulls and Equality</a>

To link to this page in other web sites use the following values.

  • Link Text : SQL Snippets: Nulls - Nulls and Equality
  • URL (href): http://www.sqlsnippets.com/en/topic-10565.html

Revision Notes

Date Category Note
2007-12-02 Updated Tutorial The SQL Snippets "Nulls and Equality" tutorial has been updated with new solutions and a discussion about the undocumented SYS_OP_MAP_NONNULL function.