title: Reuse mysql Subquery in InnerJoin
body: <p>I'm trying optimizing a query, trying to avoid repeating the query indicated with "<strong>COMPLEX QUERY</strong>", that is used 2 times and both, has the same results.</p>
  
  <p>The original query </p>
  
  <pre><code>SELECT news.* 
  FROM   news 
     INNER JOIN((SELECT myposter 
                 FROM   (SELECT **COMPLEX QUERY**)) 
                UNION 
                (SELECT myposter 
                 FROM   `profiles_old` prof2 
                 WHERE  prof2.profile_id NOT IN (SELECT **COMPLEX QUERY**))) r 
             ON news.profile = r.p 
  </code></pre>
  
  <p>I was wondering if something like this was possible:</p>
  
  <pre><code>SELECT news.* 
  FROM   (SELECT **COMPLEX QUERY**) complexQuery, 
     news 
     INNER JOIN ((SELECT myposter 
                  FROM   complexquery) 
                 UNION 
                 (SELECT myposter 
                  FROM   `profiles_old` prof2 
                  WHERE  prof2. profile NOT IN (SELECT myposter 
                                                FROM complexQuery))) r 
             ON news. profile = r.p 
  </code></pre>
  
  <p>Does Mysql do some sort of caching of that type of query?</p>