Proper SQL code for retrieving all rows from three tables, one of them is like a man-in-the-middle
This is a very simple query:

SELECT 
    u.username,
    e.address
FROM 
    Users  u
  LEFT JOIN User_Emails  ue 
    ON ue.user_id = u.user_id
  LEFT JOIN Emails  e 
    ON ue.email_id = e.email_id 
ORDER BY
    u.user_id, 
    e.address ;


You need the LEFT joins so users without emails are shown too (with NULL in the address).
You can add more columns in the SELECT list if you like, e.g. the u.user_id or the e.email_id.
The ORDER BY is also optional and can be changed according to your requirements.