Filtering on a left join in SQLalchemy
Using SQLalchemy I want to perform a left outer join and **filter out rows that DO have a match in the joined table**.

I'm sending push notifications, so I have a `Notification` table. This means I also have a `ExpiredDeviceId` 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.)

    CREATE TABLE Notification (device_id TEXT, time DATETIME);
    CREATE TABLE ExpiredDeviceId (device_id TEXT PRIMARY KEY, expiration_time DATETIME);

_Note: there may be multiple Notifications per device\_id. There is no "Device" table for each device._

So when doing `SELECT FROM Notification` I should filter accordingly. I can do it in SQL:

    SELECT * FROM Notification 
        LEFT OUTER JOIN ExpiredDeviceId 
        ON Notification.device_id = ExpiredDeviceId.device_id
    WHERE expiration_time == NULL

But how can I do it in SQLalchemy?

    sess.query(
        Notification, 
        ExpiredDeviceId
    ).outerjoin(
        (ExpiredDeviceId, Notification.device_id == ExpiredDeviceId.device_id)
    ).filter(
        ???
    )

Alternately I could do this with a `device_id NOT IN (SELECT device_id FROM ExpiredDeviceId)` clause, but that seems way less efficient.
Filtering on a left join in SQLalchemy
===

Using SQLalchemy I want to perform a left outer join and **filter out rows that DO have a match in the joined table**.

I'm sending push notifications, so I have a `Notification` table. This means I also have a `ExpiredDeviceId` 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.)

    CREATE TABLE Notification (device_id TEXT, time DATETIME);
    CREATE TABLE ExpiredDeviceId (device_id TEXT PRIMARY KEY, expiration_time DATETIME);

_Note: there may be multiple Notifications per device\_id. There is no "Device" table for each device._

So when doing `SELECT FROM Notification` I should filter accordingly. I can do it in SQL:

    SELECT * FROM Notification 
        LEFT OUTER JOIN ExpiredDeviceId 
        ON Notification.device_id = ExpiredDeviceId.device_id
    WHERE expiration_time == NULL

But how can I do it in SQLalchemy?

    sess.query(
        Notification, 
        ExpiredDeviceId
    ).outerjoin(
        (ExpiredDeviceId, Notification.device_id == ExpiredDeviceId.device_id)
    ).filter(
        ???
    )

Alternately I could do this with a `device_id NOT IN (SELECT device_id FROM ExpiredDeviceId)` clause, but that seems way less efficient.