Why does LEFT JOIN work in this query with rowid but INNER JOIN does not?

I have a simple table OriginalValidLeaders with one column LeaderType.

Leader A
Leader B
Leader C

Now I want to randomly assign them with one another, for example (it's okay if some of them do not swap):

Leader A | Leader C
Leader B | Leader A
Leader C | Leader B

In this question, the solution is to use row_number(), however for some reason, my SQLite does not support that, so I tried the following query and it works:

SELECT L.LeaderType, R.LeaderType
FROM OriginalValidLeaders L
LEFT JOIN
    (
        SELECT LeaderType
        FROM OriginalValidLeaders
        ORDER BY random()
    ) R
ON L.rowid = R.rowid;

However to be honest, it was the result of one of my luck tries, and I do not understand why it works. At first I tried INNER JOIN (which make more sense to me) but no result returned.

So my questions are:

• Why does LEFT JOIN works but not INNER JOIN?
• Does this always work for all cases?