SQL FULL JOIN

FULL JOIN (or FULL OUTER JOIN) returns all rows from both tables. If there’s no match, unmatched columns return NULL.

✅ Combines the effects of LEFT JOIN and RIGHT JOIN.

🔹 Basic Syntax

SELECT columns
FROM table1
FULL JOIN table2
  ON table1.common_column = table2.common_column;

☝️ Some DBMS (like MySQL) don’t support FULL JOIN directly — use a workaround with UNION.

🔹 Example: List All Employees and All Departments (Matched or Not)

SELECT
  e.employee_name,
  d.department_name
FROM employees e
FULL JOIN departments d
  ON e.department_id = d.department_id;

If an employee has no department → department_name is NULL
If a department has no employee → employee_name is NULL

🔹 MySQL Workaround for FULL JOIN

SELECT e.employee_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id

UNION

SELECT e.employee_name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;

🧠 Quick Recap

Key Point	Explanation
`FULL JOIN`	Returns all rows from both tables
No match?	Missing side columns are filled with `NULL`
Use case	Useful when you want to show all records, matched or not
MySQL note	Use `LEFT JOIN` + `RIGHT JOIN` with `UNION` workaround

🧩 Use FULL JOIN to see the complete picture from both tables