[zzzeek]

If the schema is known to have a FOREIGN KEY constraint on sometable.other_id, then the SQL is as close as can be to wrong without actually returning the wrong answer. In the presence of the FK, the two queries have the same intent. But the first produces a vastly complex query plan for no reason, and this has nothing at all to do with the application/model layer.

Edit: plus! even if you want the same answer assuming that FKs are not in place and that bogus values might be present in other_id, the query is still far less efficient than it should be. You should be doing this:

    select * from sometable where not exists 
    (select 1 from othertable where othertable.id=sometable.other_id)

compare the query plans on any reasonable database and see (and yes, SQLAlchemy produces the NOT EXISTS form when the relationship is a one-to-many versus many-to-one and you ask it for objects with empty collections).

[mwsherman]

“Wrong but works correctly” is a pretty novel definition of wrong.

The argument you are making is that this particular ORM needs optimizations. Which is true! The point stands that the semantics of the two expressions is different, and thus should do different things. The point also stands that the user is mixing metaphors.

If the ORM can be informed of the guarantee that the FK constraint provides, and optimize accordingly, that’s good too. But this doesn’t tell us much about ORMs, except that they can be improved.

Also, a database that has two different query plans for queries that are logically equivalent…needs improvement.