title: Filtering rows by date in a full outer join query -> missing some results
body: <h2>Background</h2>
  
  <p>I've got two tables with different types of feedback items in MySQL. I've built a query to combine these tables by <code>FULL OUTER JOIN</code> (which is actually written as two joins and an union in MySQL) and to count some average grades. This query seems to work perfectly:</p>
  
  <pre><code>  (SELECT name, AVG(l.overallQuality) AS avgLingQual,
      AVG(s.overallSatisfaction) AS avgSvcQual
    FROM feedback_linguistic AS l
    LEFT JOIN feedback_service AS s USING(name)
    GROUP BY name)
  UNION ALL
    (SELECT name, AVG(l.overallQuality) AS avgLingQual, 
      AVG(s.overallSatisfaction) AS avgSvcQual
    FROM feedback_linguistic AS l
    RIGHT JOIN feedback_service AS s USING(name)
    WHERE l.id IS NULL
    GROUP BY name)
  ORDER BY name;
  </code></pre>
  
  <p>(This is somewhat simplified for readability but it doesn't make a difference here)</p>
  
  <h2>Problem</h2>
  
  <p>Next I tried adding filtering by date (i.e. only feedback items created after a certain date are taken in account). With my SQL skills and the research I did, I was able to come up with this:</p>
  
  <pre><code>  (SELECT name, AVG(l.overallQuality) AS avgLingQual,
      AVG(s.overallSatisfaction) AS avgSvcQual
    FROM feedback_linguistic AS l
    LEFT JOIN feedback_service AS s USING(name)
    WHERE (s.createdTime &gt;= '" &amp; date &amp; "' OR s.createdTime IS NULL)
      AND (l.createdTime &gt;= '" &amp; date &amp; "' OR l.createdTime IS NULL)
    GROUP BY name)
  UNION ALL
    (SELECT name, AVG(l.overallQuality) AS avgLingQual, 
      AVG(s.overallSatisfaction) AS avgSvcQual
    FROM feedback_linguistic AS l
    RIGHT JOIN feedback_service AS s USING(name)
    WHERE l.id IS NULL
      AND (s.createdTime &gt;= '" &amp; date &amp; "' OR s.createdTime IS NULL)
    GROUP BY name)
  ORDER BY name;
  </code></pre>
  
  <p>This <em>almost</em> works: the results I get look about right. However, a couple of feedback items are missing. For example, setting the date one month ago, I counted feedback for 21 different people in the database, but this query only returns 19 people. The worst thing is that I can't seem to find any similarities between the missing items.</p>
  
  <p>Am I doing something wrong in this query? I think that the <code>WHERE</code> clause does the date filtering after the <code>JOIN</code> and ideally I would probably be doing it before. Then again, I don't know if this causes my problem and I also have no idea how to write this query differently.</p>