Is it correct to use data from different tables without joins?

Question

SQL

In general, I found such a fragment in theory.

SELECT gname FROM Goods
WHERE id_g NOT IN (
SELECT good FROM Payments WHERE Year(PDate) = 2005
)

Is this entry correct? After all, in the subquery, we take data from another table, and we do not perform any joins. Since we are entering data from another table into the NOT IN operator which refers to the id_g field, which is simply a key one.
Or is there something related to foreign keys?

Answer 1

In principle, such a request has the right to life.
Moreover, a modern optimizer will build an execution plan identical to a query with a join.
Modern sql is a smart enough thing and fixes a lot of clumsy things from the user