Q:

Struggling to interpret this SQL join syntax

Two pretty similar queries:
Query #1:
SELECT *
FROM employee e
LEFT JOIN employee_payments ep
INNER JOIN payments p ON ep.payment_id = p.id
    ON ep.employee_id = e.id

Query #2:
SELECT *
FROM employee e
LEFT JOIN employee_payments ep ON ep.employee_id = e.id
INNER JOIN payments p ON ep.payment_id = p.id

But obviously crucially different syntax.
The way I learn these new syntax concepts best are to interpret them as plain English. So how could you describe what these are selecting?
I would expect that they'd produce the same results, but it feels to me like the LEFT JOIN in the second query acts as an INNER JOIN somehow - since a fraction of my results set are returned (i.e. the employees with payments).
If the first query 'says' "give me all employees, along with any available employee_payments (that have already been joined with their payment record)"- what does the second query say?

A:

If the first query 'says' "give me all employees, along with any available employee_payments (that have already been joined with their payment record)"- what does the second query say?

I suppose you might put it as "Take all employees along with any available employee_payments. Join this with the payment records."
The "Join this with the payment records" is what filters out employees that don't have any associated employee_payments records: the attempt to join with the payment records will fail.

but it feels to me like the LEFT JOIN in the second query acts as an INNER JOIN somehow

It's not the LEFT JOIN that's doing the filtering, but it does indeed give the exact same result as if the LEFT JOIN had been an INNER JOIN.

