python

Left Join Query python Sqlalchemy

If you want to implement outer join in Sqlalchemy then you can pass isouter = True to your join query.

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

Was this helpful?