I am confused about the different ways to do select queries. The first way I see is to use direct table object and its select() function. However, if the query requires joins operations it seems you do not join through the table objects but instead use session.query().
Why are there different ways to perform these queries, and when should I use each method?
You are seeing examples from two different layers of SQLAlchemy.
The core layer (select) typically deals with tables directly, and is sometimes more verbose about constructing queries. The orm layer (session.query) typically deals with mapped classes and provides some conveniences for joining and filtering.
Either one is fully capable of performing complex queries.
The core layer can be more appropriate for larger queries, at the expense of a less expressive results than the full mapped classes the orm provides.
Typically, it is easier and more beginner friendly to write applications at the orm layer, and drop to the core layer only when needed. I would advise reading through both the linked pages to familiarize yourself with both and decide which is appropriate for you.
