Is such a JOIN query optimal?

Question

SQL

Hello. I have the following database structure:

T1
id

T2
id

T3
id
t2_id
t1_id

You need to write such a query to select from table T2 all records for which there is an associated record in T3. You need to select by id from T1.
Well, that is, we need all T2 for which there are records from T3 by id from T1.
Wrote such a JOIN

select "T2".* from "T2" inner join "T3" on "T3"."t2_id" = "T2"."id" WHERE "T3"."t1_id" " = ?

I get what I want. but do I understand correctly that with such a query, all records are still selected through JOIN, and then WHERE is done on them? Or if there is a LIMIT, for example 100, then you can not worry about the load? And do I need to somehow do a JOIN of 3 tables?
PS if you didn't explain well, you can, for example, explain the problem like this. There are users, products and orders. You need to select all the products that the user bought.

Answer 1

You can see the real order of operations using EXPLAIN - it depends on the decisions made by the optimizer. Which in turn depend on data size indexes and data distribution and factors specific to a particular DBMS.