session.query( Employee_Model.first_name, Employee_Model.last_name, Employee_Model.email, Employee_Address_Model.address ).join( Employee_Address_Model, Employee_Address_Model.employee_id == Employee_Model.id, isouter = True #Add this to implement left outer join ).all()
In the code snippet, we are joining two tables 'employee' and 'employee_address' which are created using models Employee_Model and Employee_Address_Model. We are implementing outer join on Employee_Address_Model so that if it does not find any entry regarding employee_id it will return null as address and will not remove row as inner join does.
It will generate the below SQL query:
SELECT employee.first_name, employee.last_name, employee.email, employee_address.address FROM employee LEFT JOIN employee_address ON employee_address.employee_id = employee.id