title: SQL subquery and Joins giving same or different result (oracle)
body: <p>I'm working to optimize queries due to huge amount of data on Oracle.</p>
  
  <p>There is one query like this.</p>
  
  <p>With subquery :</p>
  
  <pre><code>SELECT
    STG.ID1,
    STG.ID2
  FROM (SELECT 
          DISTINCT
          H1.ID1,
          H2.ID2
        FROM T_STGDV STG
        INNER JOIN T_HUB1 H1 ON STG.BK1 = H1.BK1
        INNER JOIN T_HUB2 H2 ON STG.BK2 = H2.BK2 ) STG
  LEFT OUTER JOIN T_LINK L ON  L.ID1 = STG.ID1 AND L.ID2 = STG.ID2
  WHERE L.IDL IS NULL;
  </code></pre>
  
  <p>I'm doing this optimization : </p>
  
  <pre><code>SELECT 
    DISTINCT
    H1.ID1,
    H2.ID2
  FROM T_STGDV STG
  INNER JOIN T_HUB1 H1 ON STG.BK1 = H1.BK1
  INNER JOIN T_HUB2 H2 ON STG.BK2 = H2.BK2 
  LEFT OUTER JOIN T_LINK L ON L.ID1 = H1.ID1 AND L.ID2 = H2.ID2
  WHERE L.IDL IS NULL;
  </code></pre>
  
  <p>I want to know if the result will be the same, the behavior is the same.</p>
  
  <p>I did some tests, I didn't find difference but maybe i missed some test case ?</p>
  
  <p>Any idea what could be the difference between those queries ?</p>
  
  <p>Thanks.</p>
  
  <p>Some details, the Explain plan for those testing tables (the cost are not representative of the real tables)</p>
  
  <p><strong>the First query :</strong> </p>
  
  <pre><code>Plan hash value: 2680307749
  
  -----------------------------------------------------------------------------------
  | Id  | Operation               | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
  -----------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT        |         |     1 |    65 |    11  (28)| 00:00:01 |
  |*  1 |  FILTER                 |         |       |       |            |          |
  |*  2 |   HASH JOIN OUTER       |         |     1 |    65 |    11  (28)| 00:00:01 |
  |   3 |    VIEW                 |         |     1 |    26 |     8  (25)| 00:00:01 |
  |   4 |     HASH UNIQUE         |         |     1 |   134 |     8  (25)| 00:00:01 |
  |*  5 |      HASH JOIN          |         |     1 |   134 |     7  (15)| 00:00:01 |
  |*  6 |       HASH JOIN         |         |     1 |    94 |     5  (20)| 00:00:01 |
  |   7 |        TABLE ACCESS FULL| T_STGDV |     1 |    54 |     2   (0)| 00:00:01 |
  |   8 |        TABLE ACCESS FULL| T_HUB1  |     2 |    80 |     2   (0)| 00:00:01 |
  |   9 |       TABLE ACCESS FULL | T_HUB2  |     2 |    80 |     2   (0)| 00:00:01 |
  |  10 |    TABLE ACCESS FULL    | T_LINK  |     3 |   117 |     2   (0)| 00:00:01 |
  -----------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
  
     1 - filter("L"."IDL" IS NULL)
     2 - access("L"."ID2"(+)="STG"."ID2" AND "L"."ID1"(+)="STG"."ID1")
     5 - access("STG"."BK2"="H2"."BK2")
     6 - access("STG"."BK1"="H1"."BK1")
  
  Note
  -----
     - dynamic sampling used for this statement (level=2)
  </code></pre>
  
  <p><strong>the second query</strong></p>
  
  <pre><code>Plan hash value: 2149614538
  
  -----------------------------------------------------------------------------------
  | Id  | Operation               | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
  -----------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT        |         |     1 |    65 |    11  (28)| 00:00:01 |
  |   1 |  HASH UNIQUE            |         |     1 |    65 |    11  (28)| 00:00:01 |
  |*  2 |   FILTER                |         |       |       |            |          |
  |*  3 |    HASH JOIN OUTER      |         |     1 |    65 |    10  (20)| 00:00:01 |
  |   4 |     VIEW                |         |     1 |    26 |     7  (15)| 00:00:01 |
  |*  5 |      HASH JOIN          |         |     1 |   134 |     7  (15)| 00:00:01 |
  |*  6 |       HASH JOIN         |         |     1 |    94 |     5  (20)| 00:00:01 |
  |   7 |        TABLE ACCESS FULL| T_STGDV |     1 |    54 |     2   (0)| 00:00:01 |
  |   8 |        TABLE ACCESS FULL| T_HUB1  |     2 |    80 |     2   (0)| 00:00:01 |
  |   9 |       TABLE ACCESS FULL | T_HUB2  |     2 |    80 |     2   (0)| 00:00:01 |
  |  10 |     TABLE ACCESS FULL   | T_LINK  |     3 |   117 |     2   (0)| 00:00:01 |
  -----------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
  
     2 - filter("L"."IDL" IS NULL)
     3 - access("L"."ID2"(+)="H2"."ID2" AND "L"."ID1"(+)="H1"."ID1")
     5 - access("STG"."BK2"="H2"."BK2")
     6 - access("STG"."BK1"="H1"."BK1")
  
  Note
  -----
     - dynamic sampling used for this statement (level=2)
  </code></pre>