FULL OUTER JOIN in SQL

11/22/2025
All Articles

SQL FULL OUTER JOIN tutorial with examples and diagrams

FULL OUTER JOIN in SQL

FULL OUTER JOIN in SQL – SQL Tutorial for Beginners

A FULL OUTER JOIN returns all rows from both tables, matching records where possible and filling unmatched fields with NULL. It is the most complete type of join because it combines the results of LEFT JOIN and RIGHT JOIN.

This beginner‑friendly SQL tutorial covers:

  • What FULL OUTER JOIN is

  • How it works

  • Syntax

  • Examples

  • FULL OUTER JOIN vs LEFT & RIGHT JOIN

  • Real‑world use cases

  • Alternatives in MySQL (since MySQL does NOT support FULL OUTER JOIN)

🔹 What Is FULL OUTER JOIN?

A FULL OUTER JOIN returns:

  • All matching rows

  • All non-matching rows from the left table

  • All non-matching rows from the right table

If values don’t match, SQL uses NULL for missing data.

✔ FULL OUTER JOIN = LEFT JOIN + RIGHT JOIN

🔸 FULL OUTER JOIN Syntax (Standard SQL)

SELECT columns
FROM table1
FULL OUTER JOIN table2
ON table1.column = table2.column;

⚠ Note: MySQL does not support FULL OUTER JOIN directly. Workarounds are shown later.

🔸 Example Tables

🧑 users table

id name
1 Amit
2 Neha
4 Sara

📦 orders table

id user_id product
1 1 Mobile
2 3 Laptop
3 4 Tablet

Notice:

  • User 3 doesn’t exist in users table

  • User 2 has no orders

🔸 FULL OUTER JOIN Example

SELECT users.name, orders.product
FROM users
FULL OUTER JOIN orders
ON users.id = orders.user_id;

✔ Expected Output

name product
Amit Mobile
Neha NULL
Sara Tablet
NULL Laptop

Explanation:

  • Amit matched ✓

  • Neha has no orders → product = NULL

  • Sara matched ✓

  • Laptop order has no matching user → name = NULL

🔹 FULL OUTER JOIN vs LEFT JOIN vs RIGHT JOIN

Feature LEFT JOIN RIGHT JOIN FULL OUTER JOIN
Return unmatched left rows ✔ ❌ ✔
Return unmatched right rows ❌ ✔ ✔
Return only matches ❌ ❌ ❌
Most complete join ❌ ❌ ✔

🔹 FULL OUTER JOIN in MySQL (Not Supported Directly)

MySQL does not support FULL OUTER JOIN directly.
But you can simulate it using UNION of LEFT JOIN and RIGHT JOIN.

✔ FULL OUTER JOIN Alternative in MySQL

SELECT u.name, o.product
FROM users u
LEFT JOIN orders o
ON u.id = o.user_id

UNION

SELECT u.name, o.product
FROM users u
RIGHT JOIN orders o
ON u.id = o.user_id;

This returns the same result as FULL OUTER JOIN.

🔹 FULL OUTER JOIN with WHERE Conditions

Example: Getting all records, only where product contains 'lap'

SELECT *
FROM users u
FULL OUTER JOIN orders o
ON u.id = o.user_id
WHERE o.product LIKE '%lap%';

🔹 FULL OUTER JOIN With Multiple Tables

SELECT u.name, o.product, p.amount
FROM users u
FULL OUTER JOIN orders o
ON u.id = o.user_id
FULL OUTER JOIN payments p
ON o.id = p.order_id;

🔹 Real‑World Examples

✔ Customer list including those without orders & orders without customers

SELECT c.name, o.order_id
FROM customers c
FULL OUTER JOIN orders o
ON c.id = o.customer_id;

✔ All employees and their departments, including:

  • Employees without departments

  • Departments without employees

SELECT e.name, d.dept_name
FROM employees e
FULL OUTER JOIN departments d
ON e.dept_id = d.id;

✔ Students and course enrollments

SELECT s.name, c.course_name
FROM students s
FULL OUTER JOIN courses c
ON s.course_id = c.id;

🔹 Best Practices

✔ Use FULL OUTER JOIN only when needed (large performance cost)
✔ In MySQL, use LEFT JOIN + RIGHT JOIN with UNION
✔ Always index join columns
✔ Use IS NULL to find unmatched rows
✔ Keep tables clean to prevent orphan records

Summary

In this FULL OUTER JOIN tutorial, you learned:

  • What FULL OUTER JOIN does

  • How it returns both matching and non-matching rows

  • Syntax and examples

  • Differences from LEFT and RIGHT JOIN

  • MySQL alternative using UNION

FULL OUTER JOIN is powerful when you want a complete view of relationships between tables.

Article