title: Mysql exclude a subset from a set
body: <p>It's a while that I'm trying to sort this out, but to no avail!  </p>
  
  <p><strong>The problem</strong><br>
  Consider a set where there are a few subsets (i.e. 3 subsets) presented below:<br>
  <img src="https://i.stack.imgur.com/isuwK.png" alt="Collection with 3 subsets"></p>
  
  <p><strong>The Question</strong><br>
  Is there anyway that I can SELECT the COLLECTION <strong>excluding</strong> SUB3?  </p>
  
  <p>I have tried many things, such as: <code>LEFT JOIN</code>, <code>JOIN</code>, <code>NOT IN</code>, <code>NOT EXISTS</code>, and further more multiple <code>SELECT</code> sub queries.  </p>
  
  <pre><code>SELECT 
      t1.*, t2.*
  FROM
      table AS t1
          LEFT JOIN
      table AS t2 ON t1.id = t2.id
  WHERE
      t1.attribute = 'COLLECTION'
          AND t2.attribute = 'SUB3'
          AND oa2.id IS NULL;
  </code></pre>
  
  <p><strong>Note</strong><br>
  Please note that the <code>table</code> holds about 75 million entries (rows) and I'm looking to have something fast!</p>
  
  <p><strong>Update</strong><br>
  <strong>Example</strong>  </p>
  
  <pre><code>+-----+-------------+
  | id  | attribute   |
  +-----+-------------+
  |  1  | vehicle     |
  |  1  | bus         |
  |  2  | vehicle     |
  |  2  | car         |
  |  3  | vehicle     |
  |  3  | truck       |
  +-----+-------------+  
  Of course there is a second incremental id to avoid duplicate issues!
  </code></pre>
  
  <p>Thus in this example, I would want all the vehicles except the <code>truck</code>s!  </p>
  
  <p>Please advise,<br>
  Thanks</p>