SQLAlchemy: Selecting all records in one table that are not in another, related table

Question

I have two tables, ProjectData and Label, like this.

class ProjectData(db.Model):
    __tablename__ = "project_data"

    id = db.Column(db.Integer, primary_key=True)

class Label(db.Model):
    __tablename__ = "labels"

    id = db.Column(db.Integer, primary_key=True)
    data_id = db.Column(db.Integer, db.ForeignKey('project_data.id'))

What I want to do is select all records from ProjectData that are not represented in Label - basically the opposite of a join, or a right outer join, which is not a feature SQLAlchemy offers.

I have tried to do it like this, but it doesn't work.

db.session.query(ProjectData).select_from(Label).outerjoin(
        ProjectData
    ).all()

Answer 1

Finding records in one table with no match in another is known as an anti-join.

You can do this with a NOT EXISTS query:

    from sqlalchemy.sql import exists
    stmt = exists().where(Label.data_id == ProjectData.id)
    q = db.session.query(ProjectData).filter(~stmt)

which generates this SQL:

SELECT project_data.id AS project_data_id 
  FROM project_data 
  WHERE NOT (
    EXISTS (
      SELECT * 
      FROM labels 
      WHERE labels.data_id = project_data.id
    )
  )

Or by doing a LEFT JOIN and filtering for null ids in the other table:

q = (db.session.query(ProjectData)
               .outerjoin(Label, ProjectData.id == Label.data_id)
               .filter(Label.id == None)
    )

which generates this SQL:

SELECT project_data.id AS project_data_id 
  FROM project_data 
  LEFT OUTER JOIN labels ON project_data.id = labels.data_id  
  WHERE labels.id IS NULL