In Sqlalchemy What Is The Difference Between The "filter" Vs "join And Filter" Syntax?

More specifically, I see that instead of using the natural SQL-like join syntax in SQLAlchemy, a lot of people tend to use filters for purposes of joins. To elaborate here's how I

Solution 1:

The main difference is that the former results in a query that uses the SQL-92JOIN syntax, while the latter uses the older syntax – there are people that prefer it out of habit, for example. Both are the right way and neither have much to do with the code being Pythonic or not. Also in my opinion neither is more idiomatic in SQLAlchemy, though Query.join() works nicely with defined foreign key relationships and ORM relationships, as you've noted yourself. They should also result in the same execution plan in modern SQL DBMS, so there's no meaningful difference in resource usage etc.

As to Query.update() not supporting explicit joins, different SQL DBMS have varying support for multiple table updates, with differing syntax and methods. Some allow explicit joins, some don't, and some allow updating through subqueries. The current implementation seems like it's a compromise and will render to suitable UPDATE statement for the DBMS in use.