Awesome question—let’s dive into the key differences between contains_eager and joinedload in SQLAlchemy. Even though they both handle eager loading of relationships and can generate similar-looking SQL, they’re built for totally distinct use cases.

1. Who’s in Charge of the JOIN? #

• joinedload is SQLAlchemy’s "hands-off" eager loader: You just tell it which relationship to load, and it handles adding the JOIN (usually a LEFT OUTER JOIN) behind the scenes. It’s designed to avoid N+1 queries without letting you modify the parent query using the joined table. You can’t filter, sort, or alias the related table directly in the main query when using joinedload—it’s purely for fetching related data alongside your parent entities.
• contains_eager puts you in control: You write the JOIN yourself in your query, then use contains_eager to signal to SQLAlchemy: "Don’t load this relationship lazily—map the rows from my custom JOIN to the parent object’s attribute." This means you can filter, sort, or apply aliases to the joined table as part of your main query logic.

2. Impact on the Parent Result Set #

• With joinedload, SQLAlchemy automatically deduplicates parent entities. For example, if a user has 3 addresses, the JOIN would normally return 3 rows for that user, but joinedload will collapse those into a single User object with all 3 addresses attached—no extra work needed from you.
• With contains_eager, since you’re defining the JOIN yourself, the result set will include duplicate parent rows if the joined table has multiple matches. You’ll need to use distinct() or handle deduplication manually if you want a unique list of parent entities.

3. Common Use Cases #

• Use joinedload for simple eager loading: When you just want to fetch related data to avoid lazy loading, and don’t need to filter or sort based on the related table. Example:

  # Load all users and their addresses in one query, no filtering on addresses
  session.query(User).options(joinedload(User.addresses)).all()
  

• Use contains_eager for filtered/sorted eager loading: When you need to narrow down parent entities based on the related table, or use aliases for the joined data. Like your example:

  adalias = aliased(Address)
  # Only get users with addresses in New York, and load those filtered addresses
  query = session.query(User).\
      outerjoin(adalias, User.addresses).\
      filter(adalias.city == "New York").\
      options(contains_eager(User.addresses, alias=adalias)).all()
  

4. SQL Generation Nuances #

• joinedload uses internal aliases for the joined table (you won’t see these in your code) to avoid naming conflicts, even if you join multiple times. It might also switch to a separate SELECT query depending on the loading strategy.
• contains_eager uses exactly the JOIN you define—your aliases, your join conditions, all show up directly in the generated SQL.

To sum it up: joinedload is for hassle-free, automatic eager loading that leaves your parent result set untouched. contains_eager is for when you need full control over the JOIN logic and want to tie that custom joined data to your parent objects.

内容的提问来源于stack exchange，提问作者Iorrrrrrrrrr