title: Delete from table where table field does not match another table
body: <p>I have two MySQL tables, one named "stations" and another named "records." "stations" is related to the records listed in "records" by <strong>stations.id=records.stationID</strong>. </p>
  
  <p>Many of my "stations" are not related to any data which appears in the "records" table, and I would like to delete the rows of "stations" in which their corresponding id does not match any value in records.stationID.</p>
  
  <p>I have tried:</p>
  
  <pre><code>DELETE FROM stations
  WHERE stations.id NOT IN (SELECT DISTINCT records.stationID
                            FROM records);
  </code></pre>
  
  <p>which worked in principle, but I let it run for 8 hours overnight and it hadn't completed in the morning. I think this is because it requires the subquery to be executed for each record in "stations," which contains 9000+ rows. (is that correct?)</p>
  
  <p>How can I expedite the deletion process? I've tried various inner joins as well, i.e.:</p>
  
  <pre><code>DELETE FROM stations
  INNER JOIN records on stations.id=records.stationID
  WHERE stations.id not in records.stationID;
  </code></pre>
  
  <p>which gives me a syntax error. I also tried the following, which don't appear to do anything, really:</p>
  
  <pre><code>DELETE stations.*
  FROM stations
  INNER JOIN records ON records.stationID=stations.id
  WHERE stations.id NOT IN (SELECT DISTINCT records.stationID
                            FROM records);
  </code></pre>
  
  <p>and</p>
  
  <pre><code>DELETE FROM stations
  INNER JOIN records on records.stationID=stations.id
  WHERE stations.id NOT IN (SELECT DISTINCT records.stationID from records);
  </code></pre>
  
  <p>Any ideas?</p>
  
  <p><strong>SOLVED:</strong> I just needed to add an index to the "records" table. Thanks to all for the help </p>