"All joins can be rewritten to sub queries", is this statement true?
As far as I know, a JOIN [INNER JOIN], from the theoretical point of view, is a projection of a CROSS JOIN (every combination of two tables). If you can get a cross join using a subquery and apply any function on it, then you have a perfect substitute. I think you can always transform:

SELECT T1.A, T2.B
FROM T1
JOIN T2
ON condition(T1.C, T2.D)


into:

SELECT T1.A, T2.B
FROM T1
WHERE condition(T1.C, ALL(SELECT T2.D FROM T2))


A similar conclusion can be reached with LEFT [OUTER] JOINS and RIGHT [OUTER] JOINS (the other types supported on MySQL), by including the NULL value:

SELECT T1.A, T2.B
FROM T1
WHERE condition(T1.C, ALL(SELECT T2.D FROM T2) UNION SELECT NULL)