LEFT OUTER JOIN and NULL Values

I have mentioned some join operations on my previous post. LEFT OUTER JOIN takes all records of "left" table; so some of records of "right" table can stay NULL. Suppose you need to make some calculations on that NULL fields. Equality operations can not work on NULL values. NULL values are special values. If you add a condition on that field like = or <= it will skip all NULL values which are taken by OUTER JOIN. For clarity follow the example:

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr

SQL>
SQL> drop table eq_h_test;

Table dropped

SQL> create table eq_h_test( i number, z number );

Table created

SQL> INSERT INTO eq_h_test VALUES(1, 100 );

1 row inserted

SQL> INSERT INTO eq_h_test VALUES(1, 103 );

1 row inserted

SQL> INSERT INTO eq_h_test VALUES(1, 109 );

1 row inserted

SQL> INSERT INTO eq_h_test VALUES(2, 10 );

1 row inserted

SQL> INSERT INTO eq_h_test VALUES(2, 1020 );

1 row inserted

SQL> INSERT INTO eq_h_test VALUES(3, 1700 );

1 row inserted

SQL> drop table eq_test;

Table dropped

SQL> create table eq_test( i number, k varchar2(3) );

Table created

SQL> INSERT INTO eq_test VALUES( 1, 'aaa' );

1 row inserted

SQL> INSERT INTO eq_test VALUES( 2, 'fff' );

1 row inserted

SQL> INSERT INTO eq_test VALUES( 3, 'rrr' );

1 row inserted

SQL> INSERT INTO eq_test VALUES( 4, 'ttt' );

1 row inserted

SQL> INSERT INTO eq_test VALUES( 5, 'qqq' );

1 row inserted

SQL> commit;

Commit complete

Let's selecting and operating. Aggreagating functions return NULL values even if they have no record.(For more please read Aggregation Functions and NO_DATA_FOUND Exception In Oracle) I use the join field in where clause for equality. I can not get NULL values and can not get power of OUTER JOIN. So i use an extra clause that contains special equality for NULL values.
SQL> SELECT * FROM eq_test t LEFT OUTER JOIN eq_h_test h ON t.i = h.i;
         I K            I          Z
---------- --- ---------- ----------
         1 aaa          1        100
         1 aaa          1        103
         1 aaa          1        109
         2 fff          2         10
         2 fff          2       1020
         3 rrr          3       1700
         5 qqq           
         4 ttt           
8 rows selected
SQL> SELECT *
  2    FROM eq_test t
  3    LEFT OUTER JOIN eq_h_test h ON t.i = h.i
  4   WHERE h.z = (SELECT MAX(h2.z) FROM eq_h_test h2 WHERE h2.i = h.i);
         I K            I          Z
---------- --- ---------- ----------
         1 aaa          1        109
         2 fff          2       1020
         3 rrr          3       1700
SQL> SELECT *
  2    FROM eq_test t
  3    LEFT OUTER JOIN eq_h_test h ON t.i = h.i
  4   WHERE decode(h.z,
  5                (SELECT MAX(h2.z) FROM eq_h_test h2 WHERE h2.i = h.i),
  6                1,
  7                0) = 1;
         I K            I          Z
---------- --- ---------- ----------
         1 aaa          1        109
         2 fff          2       1020
         3 rrr          3       1700
         5 qqq           
         4 ttt           
SQL>  SELECT *
  2     FROM eq_test t
  3     LEFT OUTER JOIN eq_h_test h ON t.i = h.i
  4    WHERE h.z = (SELECT MAX(h2.z) FROM eq_h_test h2 WHERE h2.i = h.i)
  5       OR h.z IS NULL;
         I K            I          Z
---------- --- ---------- ----------
         1 aaa          1        109
         2 fff          2       1020
         3 rrr          3       1700
         5 qqq           
         4 ttt           
SQL> ---
You must keep in mind NULL values are special. You must handle them. An alternative way, you can use DECODE for equality of NULLs. I wrote a simple example to show this. You can also get more information in my On NULL Values   post.
SQL>
SQL> SELECT decode(NULL, NULL, 'null is null', 'null is not null') FROM dual;
DECODE(NULL,NULL,'NULLISNULL',
------------------------------
null is null
SQL> --
SQL> SELECT dummy FROM dual WHERE 1 = 1;
DUMMY
-----
X
SQL> SELECT dummy FROM dual WHERE NULL = NULL;
DUMMY
-----
SQL> SELECT dummy FROM dual WHERE NULL IS NULL;
DUMMY
-----
X
SQL>