title: Subqueries with EXISTS vs IN - MySQL
body: <p>Below two queries are subqueries. Both are the same and both works fine for me. But the problem is Method 1 query takes about 10 secs to execute while Method 2 query takes under 1 sec.</p>
  
  <p>I was able to convert method 1 query to method 2 but I don't understand what's happening in the query. I have been trying to figure it out myself. I would really like to learn what's the difference between below two queries and how does the performance gain happen ? what's the logic behind it ?</p>
  
  <p>I'm new to these advance techniques. I hope someone will help me out here. Given that I read the <a href="http://dev.mysql.com/doc/refman/5.0/en/exists-and-not-exists-subqueries.html" rel="noreferrer">docs</a> which does not give me a clue.</p>
  
  <p>Method 1 :</p>
  
  <pre><code>SELECT
     *       
  FROM
     tracker       
  WHERE
     reservation_id IN (
        SELECT
           reservation_id                                 
        FROM
           tracker                                 
        GROUP  BY
           reservation_id                                 
        HAVING
           (
              method = 1                                          
              AND type = 0                                          
              AND Count(*) &gt; 1 
           )                                         
           OR (
              method = 1                                              
              AND type = 1                                              
              AND Count(*) &gt; 1 
           )                                         
           OR (
              method = 2                                              
              AND type = 2                                              
              AND Count(*) &gt; 0 
           )                                         
           OR (
              method = 3                                              
              AND type = 0                                              
              AND Count(*) &gt; 0 
           )                                         
           OR (
              method = 3                                              
              AND type = 1                                              
              AND Count(*) &gt; 1 
           )                                         
           OR (
              method = 3                                              
              AND type = 3                                              
              AND Count(*) &gt; 0 
           )
     )
  </code></pre>
  
  <p>Method 2 :</p>
  
  <pre><code>SELECT
     *                                
  FROM
     `tracker` t                                
  WHERE
     EXISTS (
        SELECT
           reservation_id                                              
        FROM
           `tracker` t3                                              
        WHERE
           t3.reservation_id = t.reservation_id                                              
        GROUP BY
           reservation_id                                              
        HAVING
           (
              METHOD = 1 
              AND TYPE = 0 
              AND COUNT(*) &gt; 1
           ) 
           OR                                                     
           (
              METHOD = 1 
              AND TYPE = 1 
              AND COUNT(*) &gt; 1
           ) 
           OR                                                    
           (
              METHOD = 2 
              AND TYPE = 2 
              AND COUNT(*) &gt; 0
           ) 
           OR                                                     
           (
              METHOD = 3 
              AND TYPE = 0 
              AND COUNT(*) &gt; 0
           ) 
           OR                                                     
           (
              METHOD = 3 
              AND TYPE = 1 
              AND COUNT(*) &gt; 1
           ) 
           OR                                                     
           (
              METHOD = 3 
              AND TYPE = 3 
              AND COUNT(*) &gt; 0
           )                                             
     )
  </code></pre>