title: INNER JOIN after LEFT JOIN
body: <p>I have two queries that work great. And the latter is run once for each row of the former. I would like to combine them if possible for performance. The latter returns zero or one rows.</p>
  
  <p>The issue is that both queries use INNER JOINs I need to glue them with a LEFT JOIN. Is this legal, and is it well defined what will happen if an INNER JOIN is used "after" a LEFT JOIN. Lastly, will such a machination have any performance penalties over the existing approach which is "fast"?</p>
  
  <p>For reference, and I'm sorry if this is TMI, here is my first query:</p>
  
  <pre><code>SELECT
    pmt_attempts.user_id,
    CONCAT(DATE_FORMAT(attempt_time_end, '%Y') + 2, DATE_FORMAT(attempt_time_end, '-%m-%d')) as date_expired,
    course_sku,
    um_fn.meta_value AS 'shipping_first_name',
    um_ln.meta_value AS 'shipping_last_name',
    um_cr.meta_value AS 'credentials',
    pmt_orders.promocode_used,
    pmt_promocodes.sales_channel
  FROM
    pmt_attempts
  INNER JOIN
    pmt_courses 
      ON pmt_attempts.course_id = pmt_courses.course_id
  INNER JOIN
    pmt_orders 
      ON pmt_orders.order_id = pmt_attempts.order_id
  INNER JOIN
    wp_usermeta um_fn 
      ON pmt_orders.user_id = um_fn.user_id
      AND um_fn.meta_key = 'shipping_first_name'
  INNER JOIN
    wp_usermeta um_ln 
      ON pmt_orders.user_id = um_ln.user_id
      AND um_ln.meta_key = 'shipping_last_name'
  LEFT JOIN
    wp_usermeta um_cr 
      ON pmt_orders.user_id = um_cr.user_id
      AND um_cr.meta_key = 'credentials'
  INNER JOIN
    pmt_order_course 
      ON fk_order_id = pmt_attempts.order_id
      AND fk_course_id = pmt_attempts.course_id
  LEFT JOIN
    pmt_promocodes 
      ON pmt_orders.promocode_used = pmt_promocodes.promocode_text
  WHERE
    attempt_time_end &gt;= '2014-01-01' AND &lt;--- CONFIGURABLE REPORT PARAM
    attempt_time_end &lt; '2014-02-01' AND &lt;--- CONFIGURABLE REPORT PARAM
    attempt_complete = 'Y' AND
    (unenroll_date IS NULL OR unenroll_date = '0000-00-00') AND
    1
  ORDER BY
    attempt_time_end
  ;
  </code></pre>
  
  <p>and here is the second query:</p>
  
  <pre><code>SELECT
    course_sku,
    pmt_orders.order_date,
    attempt_time_end,
    pmt_order_course.unenroll_date
  FROM
    pmt_order_course
  INNER JOIN
    pmt_orders ON 
      pmt_orders.order_id = pmt_order_course.fk_order_id
  INNER JOIN
    pmt_courses ON 
      pmt_courses.course_id = pmt_order_course.fk_course_id
  LEFT JOIN
    pmt_attempts ON 
      pmt_attempts.course_id = pmt_courses.course_id AND
      pmt_attempts.order_id = pmt_orders.order_id AND 
      pmt_attempts.attempt_complete = 'Y'
  WHERE
    pmt_orders.user_id = 928 AND &lt;--- FROM FIRST QUERY ROW
    course_sku LIKE 'BL%' AND &lt;--- FIRST TWO LETTERS OF FIRST QUERY ROW + %
    pmt_orders.order_date &gt; '2012-12-14' &lt;--- FROM FIRST QUERY ROW
  ORDER BY
    pmt_orders.order_date
  LIMIT
    1
  ;
  </code></pre>
  
  <p>The purpose of this query is to find, for each order, whether the customer "renewed" and bought another of the same, or similar, product at a later time.</p>