title: How can I convert a NOT IN Clause into a left outer join in MySQL
body: <p>I am trying to convert a query for better performance from a NOT IS clause to a left outer join.</p>
  
  <p>the following is my query which works perfectly</p>
  
  <pre><code>SELECT a.company_code, account_name, legal_name FROM accounts a
  
  WHERE account_id NOT IN (SELECT DISTINCT  account_id FROM phone_calls WHERE status = 2 ) AND account_id &gt;10000
  
  ORDER BY legal_name, account_name, account_id
  </code></pre>
  
  <p>So I want to change it to a left join or some type of join that will do the same query.
  I have tried this but it is not working for me</p>
  
  <pre><code>SELECT a.company_code, a.account_name, a.legal_name, p.phone_call_id
  FROM accounts AS a
  LEFT JOIN phone_calls AS p ON p.account_id = a.account_id
  
  WHERE a.account_id &gt;= 10000 AND p.status = 2 AND p.phone_call_id IS NULL
  
  ORDER BY a.legal_name, a.account_name, a.account_id
  </code></pre>
  
  <p>my second query returns nothing always.</p>
  
  <p>I have tried this query and this gives me more results that the first query so it is not the same</p>
  
  <pre><code>SELECT a.company_code, a.account_name, a.legal_name, p.phone_call_id
  FROM accounts AS a
  LEFT JOIN phone_calls AS p ON p.account_id = a.account_id AND p.status = 2 AND a.account_id &gt;= 10000
  
  WHERE p.account_id IS NULL
  
  ORDER BY a.legal_name, a.account_name, a.account_id
  </code></pre>