title: Oracle SQL sub query vs inner join
body: <p>At first, I seen the select statement on <a href="https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_10002.htm" rel="nofollow noreferrer">Oracle Docs</a>.</p>
  
  <p><a href="https://i.stack.imgur.com/tqAPt.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/tqAPt.png" alt="enter image description here"></a></p>
  
  <p>I have some question about oracle select behaviour, when my query contain select,join,where. </p>
  
  <p>see this below for information:</p>
  
  <pre class="lang-none prettyprint-override"><code>My sample table:
  
  [ P_IMAGE_ID ]
   IMAGE_ID (PK)
   FILE_NAME
   FILE_TYPE
   ...
   ...
  
  [ P_IMG_TAG ]
   IMG_TAG_ID (PK)
   IMAGE_ID (FK)
   TAG
   ...
   ...
  </code></pre>
  
  <p>My requirement are: get distinct of image when it's tag is "70702".</p>
  
  <hr>
  
  <h2><strong>Method 1</strong>: Select -> Join -> Where -> Distinct</h2>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT DISTINCT PID.IMAGE_ID
                , PID.FILE_NAME
  FROM P_IMAGE_ID PID
  INNER JOIN P_IMG_TAG PTAG
          ON PTAG.IMAGE_ID = PID.IMAGE_ID
  WHERE PTAG.TAG = '70702';
  </code></pre>
  
  <p>I think the query behaviour should be like: </p>
  
  <p><code>join table -&gt; hint where cause -&gt; distinct select</code></p>
  
  <p>I use Oracle SQL developer to get the explain plan:</p>
  
  <p><a href="https://i.stack.imgur.com/nGh2p.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/nGh2p.png" alt="enter image description here"></a></p>
  
  <p>Method 1 cost <strong>76</strong>.</p>
  
  <hr>
  
  <h2><strong>Method 2</strong>: Select -> Where -> Where -> Distinct</h2>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT DISTINCT PID.IMAGE_ID
                , PID.FILE_NAME
  FROM P_IMAGE_ID PID
  WHERE PID.IMAGE_ID IN 
  (
    SELECT PTAG.IMAGE_ID
    FROM P_IMG_TAG PTAG
    WHERE PTAG.TAG = '70702'
  );
  </code></pre>
  
  <p>I think the second query behaviour should be like: </p>
  
  <p><code>hint where cause -&gt; hint where cause -&gt; distinct select</code></p>
  
  <p>I use Oracle SQL developer to get the explain plan too:</p>
  
  <p>Method 2 cost <strong>76</strong> too. Why? </p>
  
  <p><a href="https://i.stack.imgur.com/GjdIC.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/GjdIC.png" alt="enter image description here"></a></p>
  
  <p>I believe when I try where cause first for reduce the database process and avoid join table that query performance should be better than the table join query, but now when I test it, I am confused, why 2 method cost are equal ?
  Or am I misunderstood something ?</p>
  
  <p>List of my question here:</p>
  
  <ol>
  <li><p>Why 2 method above cost are equal ?</p></li>
  <li><p>If the result of sub select <code>Tag = '70702'</code> more than thousand or million or more, use join table should be better alright ?</p></li>
  <li><p>If the result of sub select <code>Tag = '70702'</code> are least, use sub select for reduce data query process is better alright ?</p></li>
  <li><p>When I use method 1 <code>Select -&gt; Join -&gt; Where -&gt; Distinct</code> mean the database process table joining before hint where cause alright ?</p></li>
  <li><p>Someone told me when i move hint cause <code>Tag = '70702'</code> into join cause 
  (ie. <code>INNER JOIN P_IMG_TAG PTAG ON PAT.IMAGE_ID = PID.IMAGE_ID AND PTAG.TAG = '70702'</code> ) it's performance may be better that's alright ?</p></li>
  </ol>
  
  <p>I read topic <a href="https://stackoverflow.com/questions/47433/subselect-vs-outer-join">subselect vs outer join</a> and <a href="https://stackoverflow.com/questions/1788678/sql-sub-query-or-inner-join">subquery or inner join</a> but both are for SQL Server, I don't sure that may be like Oracle database.</p>