WebCodeDoc

SQL FULL JOIN Function

Quick summary: The SQL FULL JOIN (FULL OUTER JOIN) returns all rows from both tables, matching rows where possible and filling missing values with NULLs.

SQL FULL JOIN Syntax 

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

SQL FULL JOIN Basic examples 

SELECT * FROM users FULL JOIN orders ON orders.user_id = users.id;
SQL
Output:
All users and all orders

Returns matched rows plus unmatched rows from both tables.

SQL FULL JOIN Real-world usage 

SELECT u.id, o.id FROM users u FULL JOIN orders o ON o.user_id = u.id;
SQL
Output:
Users with orders, users without orders, and orders without users

Detects orphaned rows on both sides.

SQL FULL JOIN Edge cases 

SELECT * FROM users FULL JOIN orders ON 1=0;
SQL
Output:
All rows from both tables with NULL matches

No join condition matches.

SQL FULL JOIN Common mistakes

Using FULL JOIN in MySQL

MySQL does not support FULL JOIN.

Incorrect
SELECT * FROM a FULL JOIN b ON a.id = b.id;
Correct
SELECT * FROM a LEFT JOIN b ON a.id=b.id UNION SELECT * FROM a RIGHT JOIN b ON a.id=b.id;

Emulate FULL JOIN using UNION.

SQL FULL JOIN Frequently Asked Questions

What is FULL JOIN in SQL?

Returns all rows from both tables, matching where possible.

Use case of FULL JOIN?

Combine all records from both tables.

Handles unmatched rows?

Yes, fills with NULL.

Common mistake?

Expecting only matched rows.

Difference from LEFT JOIN?

Includes unmatched rows from both sides.

Performance?

Can be heavy.

Supported in all DBs?

No (e.g. MySQL lacks native support).

Alternative in MySQL?

UNION of LEFT and RIGHT JOIN.

Used with WHERE?

Yes.

Best practice?

Use only when needed.

Used in analytics?

Yes.

Supports aliases?

Yes.

SQL FULL JOIN Related SQL Keywords