title: Filtering on a left join in SQLalchemy
body: <p>Using SQLalchemy I want to perform a left outer join and <strong>filter out rows that DO have a match in the joined table</strong>.</p>
  
  <p>I'm sending push notifications, so I have a <code>Notification</code> table. This means I also have a <code>ExpiredDeviceId</code> table to store device_ids that are no longer valid. (I don't want to just delete the affected notifications as the user might later re-install the app, at which point the notifications should resume according to Apple's docs.)</p>
  
  <pre><code>CREATE TABLE Notification (device_id TEXT, time DATETIME);
  CREATE TABLE ExpiredDeviceId (device_id TEXT PRIMARY KEY, expiration_time DATETIME);
  </code></pre>
  
  <p><em>Note: there may be multiple Notifications per device_id. There is no "Device" table for each device.</em></p>
  
  <p>So when doing <code>SELECT FROM Notification</code> I should filter accordingly. I can do it in SQL:</p>
  
  <pre><code>SELECT * FROM Notification 
      LEFT OUTER JOIN ExpiredDeviceId 
      ON Notification.device_id = ExpiredDeviceId.device_id
  WHERE expiration_time IS NULL
  </code></pre>
  
  <p>But how can I do it in SQLalchemy?</p>
  
  <pre><code>sess.query(
      Notification, 
      ExpiredDeviceId
  ).outerjoin(
      (ExpiredDeviceId, Notification.device_id == ExpiredDeviceId.device_id)
  ).filter(
      ???
  )
  </code></pre>
  
  <p>Alternately I could do this with a <code>device_id NOT IN (SELECT device_id FROM ExpiredDeviceId)</code> clause, but that seems way less efficient.</p>