title: Making a more efficient join
body: <p>Here's my query, it is fairly straightforward:</p>
  
  <pre><code>SELECT
      INVOICE_ITEMS.II_IVNUM, INVOICE_ITEMS.IIQSHP
  FROM
      INVOICE_ITEMS
  LEFT JOIN
      INVOICES
  ON 
      INVOICES.INNUM = INVOICE_ITEMS.II_INNUM
  WHERE
      INVOICES.IN_DATE
  BETWEEN
      '2010-08-29' AND '2010-08-30'
  ;
  </code></pre>
  
  <p>I have very limited knowledge of SQL, but I'm trying to understand some of the concepts like subqueries and the like. I'm not looking for a redesign of this code, but rather an explanation of why it is so slow (600+ seconds on my test database) and how I can make it faster. </p>
  
  <p>From my understanding, the left join is creating a virtual table and populating it with every result row from the join, meaning that it is processing every row. How would I stop the query from reading the table completely and just finding the <code>WHERE/BETWEEN</code> clause first, then creating a virtual table after that (if it is possible)?</p>
  
  <p>How is my logic? Are there any consistently recommended resources to get me to SQL ninja status?</p>
  
  <p>Edit: Thanks everyone for the quick and polite responses. Currently, I'm connecting over ODBC to a proprietary database that is used in the rapid application development framework called OMNIS. Therefore, I really have no idea what sort of optimization is being run, but I believe it is based loosely on MSSQL.</p>