ADVERTISEMENT

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 PointExplanation
FULL JOINReturns all rows from both tables
No match?Missing side columns are filled with NULL
Use caseUseful when you want to show all records, matched or not
MySQL noteUse LEFT JOIN + RIGHT JOIN with UNION workaround

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

ADVERTISEMENT