Outer Join in SQL

An Outer Join returns rows that have matching values in both tables plus the rows from one or both tables that do not have matching rows in the other table.

Types of Outer Joins:

Syntax:

-- LEFT OUTER JOINSELECT columnsFROM table1LEFT OUTER JOIN table2 ON table1.key = table2.key;-- RIGHT OUTER JOINSELECT columnsFROM table1RIGHT OUTER JOIN table2 ON table1.key = table2.key;-- FULL OUTER JOIN (Not supported in MySQL)SELECT columnsFROM table1FULL OUTER JOIN table2 ON table1.key = table2.key;

Example:

Given tables:

• employees (id, name, dept_id)

• departments (dept_id, dept_name)

LEFT OUTER JOIN — All employees, with their departments if any:

SELECT e.name, d.dept_nameFROM employees eLEFT OUTER JOIN departments d ON e.dept_id = d.dept_id;

• Employees without departments will show NULL for dept_name.

RIGHT OUTER JOIN — All departments, with employees if any:

SELECT e.name, d.dept_nameFROM employees eRIGHT OUTER JOIN departments d ON e.dept_id = d.dept_id;

• Departments without employees will show NULL for name.

FULL OUTER JOIN — All employees and all departments, matched where possible:

SELECT e.name, d.dept_nameFROM employees eFULL OUTER JOIN departments d ON e.dept_id = d.dept_id;

Notes:

• FULL OUTER JOIN is not supported in MySQL directly, but can be simulated using UNION of LEFT JOIN and RIGHT JOIN.

• Outer joins are useful when you want to keep unmatched rows from one or both tables.

Want me to provide an example on simulating FULL OUTER JOIN in MySQL?