While I haven't given it serious thought, I've been wishing for something like this

  SELECT u.name, r.role
  FROM users u
  JOIN user_roles r USING FOREIGN KEY
  WHERE u.id = :id

Using this syntax one could optionally specify the foreign key name as well, in case it is ambiguous. It's more explicit than NATURAL JOIN and it feels very SQL-ish to me.