intro
Let's explore everything you need to know about the FULL OUTER JOIN in SQL and how to use it to fully combine data from two tables.
In a relational database, data is often spread across multiple related tables. A JOIN query is used to retrieve this data together. You are probably familiar with INNER JOIN (the default type of join), and you might have used RIGHT JOIN and LEFT JOIN as well. But what about the FULL OUTER JOIN SQL operation?
In this guide, you will learn how to use the FULL OUTER JOIN in SQL, along with alternative approaches for databases that do not support it.
Let's dive in!
What Are the Effects of Using the SQL FULL OUTER JOIN Clause?
In SQL, the FULL OUTER JOIN operation retrieves all records from both joined tables, including unmatched rows from either table.
When a match exists, the corresponding columns are populated with values from both tables. If no match is found, NULL values are returned for the missing columns from the non-matching table—either on the side or the left.
Essentially, FULL OUTER JOIN combines the effects of both LEFT JOIN and RIGHT JOIN, ensuring that no records are excluded from either table. That shouldn’t come as a surprise because in SQL theory, LEFT JOIN and RIGHT JOIN are formally known as LEFT OUTER JOIN and RIGHT OUTER JOIN, respectively.
When to Use the FULL OUTER JOIN SQL Operation
Some of the most popular scenarios for using FULL OUTER JOIN in SQL are:
How to Use FULL OUTER JOIN in SQL
In databases that support this operation, the typical syntax for using FULL OUTER JOIN in SQL is:
1
SELECT *
2
FROM table_1
3
FULL [OUTER] JOIN table_2 ON table_1.common_column = table_2.common_column;
Note: The OUTER keyword is optional in many databases, so you can often write FULL JOIN instead.
The JOIN operation will:
Implementing FULL OUTER JOIN With UNION
Some databases, such as MySQL, do not support FULL OUTER JOIN directly.
At the same time, since a FULL OUTER JOIN is essentially the combination of a LEFT JOIN and a RIGHT JOIN, you can simulate it using a UNION ALL of both joins:
1
SELECT *
2
FROM table_1
3
LEFT JOIN table_2 ON table_1.common_column = table_2.common_column
4
5
UNION ALL
6
7
SELECT *
8
FROM table_1
9
RIGHT JOIN table_2 ON table_1.common_column = table_2.common_column;
Internally, this SQL query goes through a couple of stages outlined below:
For more information, read our guide on the SQL UNION ALL operator.
If you are wondering why to use UNION ALL and not UNION, read the StackOverflow discussion on that for more details.
FULL OUTER JOIN SQL Example
You now understand the theory and syntax behind the FULL OUTER JOIN, but everything becomes much easier with a complete example.
Note: In this section, we will run the queries in DbVisualizer, a popular and powerful visual database client that supports over 50 databases.
Suppose your database contains products and categories tables. Products can belong to a category, and categories can have multiple products. This means there may be products without a category and categories without products.
This is what the products table looks like:
The data in the products table in DbVisualizer
And here is the categories table:
The data in the categories table in DbVisualizer
Note that category_id in the products table is a foreign key referencing the categories table, as shown in the ERD-like references graph generated by DbVisualizer:
Now, assume that say you want to retrieve all product and category records, even those without a match in the other table. You can achieve this with the following FULL OUTER JOIN query:
1
SELECT *
2
FROM products p
3
FULL OUTER JOIN categories c ON p.category_id = c.id;
The result will be as follows:
The result includes all 11 rows, consisting of the combinations of all joined products and categories.
Notice that products with IDs 7, 9, and 10—which do not belong to any category—appear in the result list with NULL for category_name. Similarly, the category with ID 3—which has no associated products—also appears with NULL for product_name.
Wonderful! The how and why of using FULL OUTER JOIN in SQL should now be much clearer.
Conclusion
In this blog post, you learned how to use the FULL OUTER JOIN SQL operation. As shown in a detailed example, the process becomes much simpler with a powerful visual database client like DbVisualizer.
DbVisualizer offers a wide range of features, including visual query execution, data exploration, and table analysis. It also provides advanced capabilities like SQL formatting, query optimization, and ERD-style schema visualization. Try DbVisualizer for free today!
FAQ
What is the difference between a FULL JOIN and a FULL OUTER JOIN?
There is no difference between FULL JOIN and FULL OUTER JOIN in SQL as they are interchangeable. Both terms refer to the same operation and are simply synonyms.
What is the difference between a FULL OUTER JOIN and an INNER JOIN in SQL?
A FULL OUTER JOIN returns all records from both tables, filling unmatched columns with NULL values when no match exists. In contrast, an INNER JOIN returns only the matching records between both tables, excluding unmatched rows.
What is the SQL Server FULL OUTER JOIN syntax?
This is the SQL Server FULL OUTER JOIN syntax:
1
SELECT *
2
FROM table_1
3
FULL OUTER JOIN table_2 ON table_1.common_column = table_2.common_column;
For example:
1
SELECT customers.id, customers.name, orders.id, orders.total_amount
2
FROM customers
3
FULL OUTER JOIN orders ON customers.id = orders.customer_id;
This ensures all customers and orders appear, even if there is no match.
What is the FULL OUTER JOIN SQL Oracle syntax?
The FULL OUTER JOIN SQL Oracle syntax is:
1
SELECT *
2
FROM table_1
3
FULL OUTER JOIN table_2 ON table_1.common_column = table_2.common_column;
This example will retrieve all employees and departments, ensuring unmatched employees (without a department) and departments (without employees) will also be included with NULL values where no match exists:
1
SELECT employees.id, employees.name, departments.id, departments.department_name
2
FROM employees
3
FULL OUTER JOIN departments ON employees.department_id = departments.id;
What is the difference between the different types of SQL JOINs?
SQL JOIN types determine how tables are combined based on matching values:
For more information, read our guide on all types of SQL JOINs.
Why use a database client to deal with JOIN queries?
DbVisualizer is a robust, top-rated multi-database client that allows you to manage multiple databases from a unified platform. One of its standout features is its ability to visually display query results and illustrate table relationships within a database. This simplifies the understanding of JOIN queries and enhances the overall experience of working with relational data. Grab a 21-day DbVisualizer free trial!
