JOIN always does a cartesian product?

Question

PostgreSQL

I understand that the result will be each JOIN.
I don't understand how it works internally.
It seemed to me that it was like this:
1) INNER JOIN - a Cartesian product and then a selection of rows by condition.
2) LEFT JOIN - Cartesian product and then selection of rows by condition + add rows from the left table that did not match.

Answer 1

If you are interested in this in terms of the result, then yes, you can consider it like this: Cartesian product + string filter by condition (and adding NULLs in the case of LEFT JOIN).
If you are interested in how it works inside the DBMS, then there will not always be a Cartesian product, because this is a rather expensive operation. What specific operations the DBMS will perform can be seen by EXPLAIN - this is called a query plan. There may be NESTED LOOP (nested loop) and MERGE JOIN and other operations that the query planner deems appropriate for this particular combination of tables in this particular query (this is affected by the structure of the query, the presence of indexes, statistics collected on tables, etc. factors, which I will not go into now).