Understanding syntax is the first step to mastering the FULL OUTER JOIN in SQL. Once you get the syntax down, you can easily use it to retrieve data from multiple sources, even when the structure of the data is not consistent.
FULL OUTER JOIN Examples
Now that you understand the basic syntax structure of FULL OUTER JOIN in SQL, let’s have a little fun with it by using it on real tables.
Employees Table:
| emp_id | Name |
| 1 | Alice |
| 2 | Bob |
| 3 | Charlie |
| 4 | Diana |
Salaries Table:
| emp_id | Salaries |
| 2 | 55000 |
| 3 | 62000 |
| 5 | 50000 |
Observations:
- As Employees 2 and 3 exist in both tables, they will appear fully joined.
- Employees 1 and 4 are in Employees but not in Salaries; their salary field will be NULL.
- Employee 5 is in Salaries but not in Employees; their name will be NULL.
Now that we have a visual understanding of how FULL OUTER JOIN combines data from 2 tables, let’s move on to some simple queries.
Using the two tables above, let’s explore some SQL FULL OUTER JOIN examples.
1. Basic SQL FULL OUTER JOIN
SELECT e.emp_id, e.name, s.salary
FROM Employees e
FULL OUTER JOIN Salaries s
ON e.emp_id = s.emp_id;
Output:
| emp_id | Name | salary |
| 1 | Alice | NULL |
| 2 | Bob | 55000 |
| 3 | Charlie | 62000 |
| 4 | Diana | NULL |
| 5 | NULL | 50000 |
2. FULL OUTER JOIN With WHERE clause
SELECT e.emp_id, e.name, s.salary
FROM Employees e
FULL OUTER JOIN Salaries s
ON e.emp_id = s.emp_id
WHERE s.salary IS NULL OR e.name IS NULL;
Let’s use this query to find records that do not have a match in the other table.
Output:
| emp_id | name | salary |
| 1 | Alice | NULL |
| 4 | Diana | NULL |
| 5 | NULL | 50000 |
3. SQL FULL OUTER JOIN with AS Alias
Using aliases (via the AS keyword) in SQL can make your queries more concise and readable, especially when working with longer table names or multiple joins. In the context of a FULL OUTER JOIN, aliases are helpful for referencing each table more efficiently.
Code:
SELECT
e.emp_id,
e.name,
s.salary
FROM Employees AS e
FULL OUTER JOIN Salaries AS s
ON e.emp_id = s.emp_id;
Output:
| emp_id | name | salary |
| 1 | Alice | NULL |
| 2 | Bob | 55000 |
| 3 | Charlie | 62000 |
| 4 | Diana | NULL |
| 5 | NULL | 50000 |
This output shows the result of a FULL OUTER JOIN, now written using table aliases for improved readability. It’s functionally the same as the basic FULL OUTER JOIN query, but cleaner and easier to maintain, especially when dealing with large queries or complex joins.
Common Mistakes and Tips
As powerful as FULL OUTER JOIN is, it can be intimidating to novice programmers due to its multi-table nature. Be sure to avoid these common mistakes that beginner programmers make while implementing the FULL OUTER JOIN on your own.
1. Misusing WHERE after OUTER JOIN
A frequent mistake that beginners tend to make is passing a condition to the FULL OUTER JOIN by using a WHERE clause, which filters out unmatched rows. This defeats the purpose of using the FULL OUTER JOIN.
2. Not accounting for NULL values.
Since the FULL OUTER JOIN includes unmatched records from both sides, your result set will often contain NULL values. You will need to add further logic to handle this scenario.
Joining large tables using the FULL OUTER JOIN can use up a lot of system resources, sometimes resulting in slow performance.
4. Assuming All SQL Dialects Support FULL OUTER JOIN
FULL OUTER JOIN is not natively supported in all database systems. For example, MySQL does not support it. Learn more about MySQL queries from our free resource.
FULL OUTER JOIN vs Other Joins
At the core of understanding the FULL OUTER JOIN is comparing it to other SQL joins. Each join serves a special purpose, and it is important to understand their differences to use them effectively.
| Join Type | Returns | Excludes |
| INNER JOIN | Only rows with matching keys in both tables | Unmatched rows from both tables |
| LEFT JOIN | All rows from the left table + matching rows from right | Unmatched rows from the right table |
| RIGHT JOIN | All rows from the right table + matching rows from the left | Unmatched rows from the left table |
| FULL OUTER JOIN | All rows from both tables (matched + unmatched) | Nothing — all data is included; unmatched rows contain NULLs |
Difference Between CROSS JOIN and FULL OUTER JOIN
CROSS JOIN vs FULL OUTER JOIN is frequently misunderstood by novice SQL developers. While both are used to combine two tables in SQL, they function in different ways and are used for different use cases. Let’s have a deeper look.
| Feature | FULL OUTER JOIN | CROSS JOIN |
| Returns | All matched + unmatched rows from both tables | Cartesian product of both tables |
| Match Condition | Yes (uses ON clause to match rows) | No matching condition used |
| Output Size | Depends on data + matches | Rows = rows in Table A × rows in Table B |
| Use Case | Compare or merge two related datasets | Generate combinations or test cases |
Use CROSS JOIN with caution on large data sets as it can easily create millions of rows, consuming immense system resources and eventually leading to a system crash.
Conclusion
We have so far covered the FULL OUTER JOIN, its uses, and separating factors from other SQL joins. FULL OUTER JOIN is one of many commonly asked SQL Interview questions. If you are preparing for your technical SQL interview, check out our top 100 SQL Interview questions to get a head start. You can also use our SQL cheat sheet to have quick access to all kinds of queries and keywords at a glance.
Full Outer Join – FAQs
1. Is FULL OUTER JOIN supported in MySQL?
Ans. While the FULL OUTER JOIN keyword is not directly supported in MySQL, you can simulate it by combining a LEFT and a RIGHT JOIN with UNION like so:
SELECT *
FROM table1
LEFT JOIN table2 ON table1.id = table2.id
UNION
SELECT *
FROM table1
RIGHT JOIN table2 ON table1.id = table2.id;
2. What is the difference between a FULL OUTER JOIN and a UNION?
Ans. Both FULL OUTER JOIN and UNION are used to combine data from two tables, but they serve different purposes and are used for different scenarios.
| Aspect |
Join |
Union |
| Columns |
Joins columns side by side from both tables |
Requires the same number and type of columns in both SELECTs |
| Result Content |
Matched + unmatched rows from both tables |
All rows from both SELECT queries (like a vertical merge) |
| NULLs for Missing Data |
Yes, for unmatched columns |
No joining of rows, rows are separate |
3. Can the WHERE clause be used with a FULL OUTER JOIN?
Ans. Yes, you can use the WHERE clause with a FULL OUTER JOIN, but you need to implement it carefully. The FULL OUTER JOIN includes unmatched rows by design, and adding a WHERE clause might unintentionally exclude NULL values.
4. When should a FULL OUTER JOIN be avoided?
Ans. FULL OUTER JOIN should only be used when you need all records from both tables. For all other needs and purposes, other SQL joins are a better option and easier to maintain.
5. Is FULL OUTER JOIN the same as FULL JOIN?
Ans. Yes, FULL OUTER JOIN and FULL JOIN are the same thing, as they are just two ways of writing the same SQL operation.Most SQL databases accept both forms, and there is no functional difference between them.