SQL FULL JOIN

Add W3Buddy as a preferred source on Google

हि En

🔊 Listen

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

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