title: Select values from a list that are not in a table
body: <p>I have a list of ids:</p>
  
  <pre><code>(1, 2, 3, 6, 7)
  </code></pre>
  
  <p>And a table:</p>
  
  <pre><code>id | anothercolumn
  ------------------
   1 | NULL
   2 | foo
   4 | bar
   5 | NULL
   6 | NULL
  </code></pre>
  
  <p>I want to retrieve the values from my list which are not ids of my table.</p>
  
  <p>Expected result:</p>
  
  <pre><code>3, 7
  </code></pre>
  
  <h3>SELECT without FROM</h3>
  
  <p>I tried something like this:</p>
  
  <pre><code>SELECT i
  WHERE i IN (1, 2, 3, 6, 7)
  AND i NOT IN (SELECT id FROM mytable);
  </code></pre>
  
  <p>But this is not a valid MySQL query (a <code>FROM</code> is required).</p>
  
  <h3>UNION</h3>
  
  <p>There is also this possibility:</p>
  
  <pre><code>SELECT i
  FROM (
      SELECT 1 AS i
      UNION SELECT 2
      UNION SELECT 3
      UNION SELECT 6
      UNION SELECT 7 ) AS mylistofids
  LEFT JOIN mytable
  ON mytable.id = i
  WHERE mytable.id IS NULL;
  </code></pre>
  
  <p>This works, but if my list of ids becomes bigger, the query will soon be huge ...</p>
  
  <h3>Temporary Table</h3>
  
  <p>I can also create a temporary table for my list of ids:</p>
  
  <pre><code>CREATE TEMPORARY TABLE mylistofids (
      i INT
  );
  
  INSERT INTO mylistofids (i) VALUES (1), (2), (3), (6), (7);
  </code></pre>
  
  <p>Then use it in a <code>LEFT JOIN</code>:</p>
  
  <pre><code>SELECT i
  FROM mylistofids
  LEFT JOIN mytable
  ON mytable.id = i
  WHERE mytable.id IS NULL;
  </code></pre>
  
  <p>This works too, but in my case, I don't have the rights to create a table (temporary or not).</p>
  
  <p>Do you see a way to solve this problem in the nicest way possible?</p>