title: What's the best way to use LEFT OUTER JOIN to check for non-existence of related rows
body: <p>Using MySQL 5.x I want to <strong>efficiently</strong> select all rows from table X where there is <strong>no related row</strong> in table Y satisfying some condition, e.g.</p>
  
  <p>Give me all records in X where a related Y with foo = bar does NOT exist</p>
  
  <pre><code>SELECT count(id) FROM X
  LEFT OUTER JOIN Y ON y.X_id = X.id AND y.foo = 'bar'
  WHERE y....?
  </code></pre>
  
  <p>As I understand it, a left outer join is guaranteed to produce a row for each row in the left (first) table -- X in this case -- whether or not a satisfying row in the joined table was found. What I want to do is then select only those rows where no row was found.</p>
  
  <p>It seems to me that y.X_id should be NULL if there is no matching record, but this test doesn't seem to work. Nor does y.X_id = 0 or !y.X_id.</p>
  
  <p><strong>Edits</strong>: corrected transcription error (ON not AS) which was pointed out by several responses. Fixed grammatical error.</p>