title: NETEZZA: Can LEFT JOIN be faster than INNER JOIN?
body: <p>Query:</p>
  
  <pre><code>SELECT * FROM TABLE1 TBL
  INNER JOIN CROSS_REF_TABLE XREF
  ON TBL.COL = XREF.COL
  </code></pre>
  
  <p>VS</p>
  
  <pre><code>SELECT * FROM TABLE1 TBL
  LEFT JOIN CROSS_REF_TABLE XREF
  ON TBL.COL = XREF.COL
  WHERE XREF.COL IS NOT NULL
  </code></pre>
  
  <p>Can Left join be faster than inner join ? In my case for some reason I see the LEFT JOIN is faster.</p>
  
  <p>NOTE: All the tables have random distribution.</p>
  
  <p>When I look at the execution plan the difference between two is with the added distribution on cross reference table.</p>
  
  <p>LEFT JOIN:</p>
  
  <pre><code>Node 32.
    [SPU Sequential Scan table "CROSS_REF_TABLE XREF" as "XREF" {}]
        -- Estimated Rows = 129044, Width = 18, Cost = 0.0 .. 0.2, Conf = 100.0
        Projections:
          1:XREF.ORG_SK  2:XREF.COL
    [SPU Distribute on {(XREF.COL)}]
    [HashIt for Join] 
  </code></pre>
  
  <p>INNER JOIN:</p>
  
  <pre><code>Node 3.
    [SPU Sequential Scan table "CROSS_REF_TABLE XREF" as "XREF" {}]
        -- Estimated Rows = 129044, Width = 18, Cost = 0.0 .. 0.4, Conf = 100.0
        Projections:
          1:XREF.ORG_SK  2:XREF.COL
    [HashIt for Join] 
  </code></pre>
  
  <p>Does Netezza LEFT JOIN do Nested Loop or Hash Join?</p>