title: What is better? Subqueries or inner joining ten tables?
body: <p>An old system have arrived on our office for some changes and fix, but it is also suffering from performance issues. We don't know exactly what is the source of this slowness.</p>
  
  <p>While we were refactoring the old code we found several sql queries with the follow pattern (the queries are simplified for example purpose):</p>
  
  <pre><code>SELECT
     (
      SELECT X
      FROM A
      WHERE A.id = TABLE.id
     ) AS COLUMN1,
      (
      SELECT Y
      FROM B
      WHERE B.id = TABLE.id
     ) AS COLUMN1,
     (
      SELECT Z
      FROM C
      WHERE C.id = TABLE.id
     ) AS COLUMN1,
     ...
  FROM
      TABLE
  WHERE
      TABLE.id = @param;
  </code></pre>
  
  <p>These queries do several internal sub queries from <strong>every</strong> column they return.</p>
  
  <p>We are planning to rewrite these queries on the follow pattern:</p>
  
  <pre><code>SELECT
      A.X, B.Y, C.Z
  FROM
      TABLE
      INNER JOIN A on A.ID = TABLE.ID
      INNER JOIN B on B.ID = TABLE.ID
      INNER JOIN C on C.ID = TABLE.ID
  WHERE
      TABLE.id = @param;
  </code></pre>
  
  <p>With inner joins they are <strong>easier to read and understand</strong>, but is it really any faster? Is it the better way to write them?
  Unfortunately the first one we rewrote didn't improve the query time, it made the query a bit slower.</p>
  
  <p>Here is my question: should we rewriting all these queries? Are these sub-queries a good way to do this job? Are they faster the the inner-join way?</p>