title: Queries within queries: Is there a better way?
body: <p>As I build bigger, more advanced web applications, I'm finding myself writing extremely long and complex queries. I tend to write queries within queries a lot because I feel making one call to the database from PHP is better than making several and correlating the data.</p>
  
  <p>However, anyone who knows anything about SQL knows about <code>JOIN</code>s. Personally, I've used a <code>JOIN</code> or two before, but quickly stopped when I discovered using subqueries because it felt easier and quicker for me to write and maintain.</p>
  
  <p>Commonly, I'll do subqueries that may contain one or more subqueries from relative tables.<br>
  Consider this example:</p>
  
  <pre><code>SELECT 
    (SELECT username FROM users WHERE records.user_id = user_id) AS username,
    (SELECT last_name||', '||first_name FROM users WHERE records.user_id = user_id) AS name,
    in_timestamp,
    out_timestamp
  FROM records
  ORDER BY in_timestamp
  </code></pre>
  
  <p>Rarely, I'll do subqueries after the <code>WHERE</code> clause.<br>
  Consider this example:</p>
  
  <pre><code>SELECT
    user_id,
    (SELECT name FROM organizations WHERE (SELECT organization FROM locations WHERE records.location = location_id) = organization_id) AS organization_name
  FROM records
  ORDER BY in_timestamp
  </code></pre>
  
  <p>In these two cases, would I see any sort of improvement if I decided to rewrite the queries using a <code>JOIN</code>?</p>
  
  <p>As more of a blanket question, what are the advantages/disadvantages of using subqueries or a <code>JOIN</code>? Is one way more correct or accepted than the other?</p>