One to Many Relation in RDBMS single SQL to fetch data

Many times we need a single sql that fetch the data from one to many relation ship.
in mysql database we can achive this with the help of OUTER JOIN.

let say employee table having the record with emp_id and each employee have multiple contact number in table emp_contact so we fetch the record in single sql employee with there contact list.

SELECT
emp.*,
con.contact_number_list
FROM
emp
LEFT OUTER JOIN (
SELECT emp_id, GROUP_CONCAT( CAST(contact_num as CHAR ) ) as contact_number_list
FROM emp_contact
GROUP BY emp_id
) AS con ON con.emp_id = emp.emp_id