title: Oracle - Table alias and NULL evaluation in join
body: <p>I was just trying to make an example to explain how <code>NULL</code> in Oracle can lead to 'unexpected' behaviours, but I've found something I did not expect... </p>
  
  <p>setup:</p>
  
  <pre><code>create table tabNull (val varchar2(10), descr varchar2(100));
  insert into tabNull values (null, 'NULL VALUE');
  insert into tabNull values ('A', 'ONE CHAR');
  </code></pre>
  
  <p>This gives what I expected:</p>
  
  <pre><code>SQL&gt; select * from tabNull T1 inner join tabNull T2 using(val);
  
  VAL        DESCR                DESCR
  ---------- -------------------- --------------------
  A          ONE CHAR             ONE CHAR
  </code></pre>
  
  <p>If I remove table aliases, I get:</p>
  
  <pre><code>SQL&gt; select * from tabNull inner join tabNull using(val);
  
  VAL        DESCR                DESCR
  ---------- -------------------- --------------------
  A          ONE CHAR             ONE CHAR
  A          ONE CHAR             ONE CHAR
  </code></pre>
  
  <p>and this is quite surprising to me.</p>
  
  <p>A reason can be found in the execution plans for the two queries; with table aliases, Oracle makes an HASH JOIN and then checks for <code>T1.val = T2.val</code>:</p>
  
  <pre><code>------------------------------------------------------------------------------
  | Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
  ------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT   |         |     1 |   118 |     7  (15)| 00:00:01 |
  |*  1 |  HASH JOIN         |         |     1 |   118 |     7  (15)| 00:00:01 |
  |   2 |   TABLE ACCESS FULL| TABNULL |     2 |   118 |     3   (0)| 00:00:01 |
  |   3 |   TABLE ACCESS FULL| TABNULL |     2 |   118 |     3   (0)| 00:00:01 |
  ------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
  
     1 - access("T1"."VAL"="T2"."VAL")
  </code></pre>
  
  <p>Without aliases, it first filters one occurrence of the table for not null values, thus picking only one row, and then it makes a CARTESIAN with the second occurrence, thus giving two rows; even if it's correct, I would expect the result of a cartesian, but I don't have any row with DESCR = 'NULL VALUE'.</p>
  
  <pre><code>--------------------------------------------------------------------------------
  | Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
  --------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT     |         |     2 |   118 |     6   (0)| 00:00:01 |
  |   1 |  MERGE JOIN CARTESIAN|         |     2 |   118 |     6   (0)| 00:00:01 |
  |*  2 |   TABLE ACCESS FULL  | TABNULL |     1 |    59 |     3   (0)| 00:00:01 |
  |   3 |   BUFFER SORT        |         |     2 |       |     3   (0)| 00:00:01 |
  |   4 |    TABLE ACCESS FULL | TABNULL |     2 |       |     3   (0)| 00:00:01 |
  --------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
  
     2 - filter("TABNULL"."VAL" IS NOT NULL)
  </code></pre>
  
  <p>Is this somehow correct / expected? Isn't the result value of the cartesian even stranger than the number of returned rows? Am I misunderstanding the plans, or missing something so big that I can't see?</p>