SQL Full Outer Join
What is Full Outer Join in SQL?
A Full Outer Join in SQL is used to combine rows from two or more tables based on a related column between them. Unlike INNER JOINs which return only matched rows, and LEFT/RIGHT JOINs which return matched rows plus all rows from one table, a FULL OUTER JOIN returns all rows from both tables. This means it includes:
- - All matched rows from both tables.
- - All unmatched rows from the left table with NULLs for the right table columns.
- - All unmatched rows from the right table with NULLs for the left table columns.
This type of join is particularly useful when you need a complete overview of the data from both tables, ensuring that no information is excluded.
Visual Representation of SQL Full Outer Join
Practical Use Cases for Full Outer Join
- Data Reconciliation: When combining financial records from two different departments where some entries might not have corresponding records in the other department.
- Inventory Management: Merging lists of products from different suppliers to get a complete view of available and missing items.
- Customer Data Integration: Integrating customer databases from different regions where not all customers are present in each region's database.
Syntax:
SELECT * FROM table1 FULL OUTER JOIN table2 ON table1.column_name=table2.column_name;
Explanation:
- SELECT *: Selects all columns from the resulting table.
- FROM table1: Specifies the first table to join.
- FULL OUTER JOIN table2: Specifies the second table and the type of join (FULL OUTER).
- ON table1.column_name = table2.column_name: The condition that defines how the tables are joined, typically based on matching column values.
Syntax diagram - FULL OUTER JOIN
Example: SQL Full Outer Join
Let’s combine the same two tables using a full join.
SQL Code:
-- Selecting all columns from both 'table_A' and 'table_B'
SELECT *
-- Performing a FULL OUTER JOIN between 'table_A' and 'table_B'
FROM table_A
FULL OUTER JOIN table_B
-- Matching rows from 'table_A' and 'table_B' where the values in column 'A' are equal
ON table_A.A = table_B.A;
Explanation:
- This SQL query is retrieving data from two tables: 'table_A' and 'table_B'.
- The asterisk (*) is used to select all columns from both tables.
- The query performs a FULL OUTER JOIN between 'table_A' and 'table_B'.
- The join condition is specified in the ON clause, which matches rows based on the equality of values in column 'A' between the two tables.
- A FULL OUTER JOIN returns all rows from both tables, matching them where possible and including NULLs where there is no match.
- This type of join ensures that all rows from both tables are included in the result set, even if there are no matches between them.
- The query retrieves a combined result set that includes all rows from both tables, where applicable, based on the values in column 'A'.
- This query is useful for combining data from two tables while ensuring that no data is lost, as it includes both matched and unmatched rows from both tables.
Output:
Because this is a full join, all rows (both matching and nonmatching) from both tables are included in the output. There is only one match between table table_A and table table_B, so only one row of output displays values in all columns. All remaining rows of output contain only values from table table_A or table table_B, with the remaining columns set to missing values
only one row of output displays values in all columns explain below -
Example: Combining Sales and Returns Data
Imagine you have two tables: `Sales` and `Returns`. We want to create a report that shows all sales and all returns, regardless of whether there is a match.
SQL Code:
SELECT Sales.TransactionID, Sales.ProductID,
Sales.Quantity, Returns.ReturnID, Returns.ReturnDate
FROM Sales
FULL OUTER JOIN Returns
ON Sales.TransactionID = Returns.TransactionID;
Explanation:
This query combines all sales transactions with all return records. For transactions that have a corresponding return, it shows details from both tables. For transactions with no returns, it shows NULLs for the return details, and vice versa.
Example: Employee and Department Data Integration
Imagine you have two tables: 'Employees' and 'Departments'. We want to create a report that shows all employees and all departments, regardless of whether there is a match.
SQL Code:
SELECT Employees.EmployeeID, Employees.Name, Departments.DepartmentName
FROM Employees
FULL OUTER JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;
Explanation:
This query lists all employees and all departments. If an employee does not belong to any department, their department details are shown as NULL. If a department has no employees, the employee details are shown as NULL.
Common Pitfalls and Performance Tips for Full Outer Joins
Pitfalls:
- Large Result Sets: Since FULL OUTER JOIN returns all rows from both tables, the result set can be very large, especially if both tables have many rows. This can lead to high memory usage and slow performance.
- NULL Handling: FULL OUTER JOIN includes unmatched rows with NULLs. Be careful with NULL values when performing further operations on the result set as they can affect calculations and aggregations.
Performance Tips:
- Limit Columns: Instead of using `SELECT *`, specify only the columns you need. This reduces the amount of data processed and returned.
- Filter Early: Use WHERE clauses to filter rows as early as possible to reduce the size of the result set before the join operation.
- Indexing: Ensure that the columns used in the join condition are indexed to speed up the join operation.
Example: SQL Full Outer Join between two tables
Here is an example of full outer join in SQL between two tables.
Sample table: foods
+---------+--------------+-----------+------------+ | ITEM_ID | ITEM_NAME | ITEM_UNIT | COMPANY_ID | +---------+--------------+-----------+------------+ | 1 | Chex Mix | Pcs | 16 | | 6 | Cheez-It | Pcs | 15 | | 2 | BN Biscuit | Pcs | 15 | | 3 | Mighty Munch | Pcs | 17 | | 4 | Pot Rice | Pcs | 15 | | 5 | Jaffa Cakes | Pcs | 18 | | 7 | Salt n Shake | Pcs | | +---------+--------------+-----------+------------+
Sample table: company
+------------+---------------+--------------+ | COMPANY_ID | COMPANY_NAME | COMPANY_CITY | +------------+---------------+--------------+ | 18 | Order All | Boston | | 15 | Jack Hill Ltd | London | | 16 | Akas Foods | Delhi | | 17 | Foodies. | London | | 19 | sip-n-Bite. | New York | +------------+---------------+--------------+
As we know the FULL OUTER JOIN is the combination of the results of both LEFT OUTER JOIN and RIGHT OUTER JOIN, so, here we are going to describe how FULL OUTER JOIN perform internally.
Visual Presentation SQL Full Outer Join:
Here is the SQL statement which returns all rows from the 'foods' table and 'company' table using "FULL OUTER JOIN" clause.
SQL Code:
-- Selecting specific columns from the 'company' table and the 'foods' table
SELECT a.company_id AS "a.ComID", a.company_name AS "C_Name",
b.company_id AS "b.ComID", b.item_name AS "I_Name"
-- Performing a FULL OUTER JOIN between 'company' (aliased as 'a') and 'foods' (aliased as 'b')
FROM company a
FULL OUTER JOIN foods b
-- Matching rows from 'company' and 'foods' where the company_id values are equal
ON a.company_id = b.company_id;
Explanation:
- This SQL query is retrieving data from two tables: 'company' (aliased as 'a') and 'foods' (aliased as 'b').
- It selects specific columns from these tables, renaming them using aliases for clarity.
- The query performs a FULL OUTER JOIN between the aliased tables 'a' and 'b'.
- The join condition is specified in the ON clause, which matches rows based on the equality of 'company_id' values between the two tables.
- A FULL OUTER JOIN returns all rows from both tables, matching them where possible and including NULLs where there is no match.
- The selected columns are:
- 'company_id' from table 'a' (aliased as 'a.ComID')
- 'company_name' from table 'a' (aliased as 'C_Name')
- 'company_id' from table 'b' (aliased as 'b.ComID')
- 'item_name' from table 'b' (aliased as 'I_Name')
- The query retrieves a combined result set that includes all rows from both tables, where applicable, based on the values in 'company_id'.
- This query is useful for combining data from the 'company' and 'foods' tables while ensuring that no data is lost, as it includes both matched and unmatched rows from both tables.
Output:
a.ComID C_Name b.ComID I_Name
---------- ------------------------- ---------- -------------
16 Akas Foods 16 Chex Mix
15 Jack Hill Ltd 15 Cheez-It
15 Jack Hill Ltd 15 BN Biscuit
17 Foodies. 17 Mighty Munch
15 Jack Hill Ltd 15 Pot Rice
18 Order All 18 Jaffa Cakes
Salt n Shake
19 sip-n-Bite.
FULL OUTER JOIN using WHERE clause
We can include a WHERE clause with a FULL OUTER JOIN to get return only those rows where no matching data between the joining tables are exist.
The following query returns only those company that have no matching food product in foods, as well as that food product in foods that are not matched to the listed company.
SQL Code:
-- Selecting specific columns from the 'company' table (aliased as 'a') and the 'foods' table (aliased as 'b')
SELECT a.company_id AS "a.ComID",
a.company_name AS "C_Name",
b.company_id AS "b.ComID",
b.item_name AS "I_Name"
-- Performing a FULL OUTER JOIN between the 'company' and 'foods' tables
FROM company a
FULL OUTER JOIN foods b
-- Matching rows from 'company' and 'foods' where the company_id values are equal
ON a.company_id = b.company_id
-- Filtering the result to include only rows where either 'a.company_id' or 'b.company_id' is NULL
WHERE a.company_id IS NULL
OR b.company_id IS NULL
-- Sorting the result by the 'company_name' column in ascending order
ORDER BY company_name;
Explanation:
- This SQL query is retrieving data from two tables: 'company' (aliased as 'a') and 'foods' (aliased as 'b').
- It selects specific columns from these tables, renaming them using aliases for clarity.
- The query performs a FULL OUTER JOIN between the 'company' and 'foods' tables.
- The join condition is specified in the ON clause, which matches rows based on the equality of 'company_id' values between the two tables.
- A FULL OUTER JOIN returns all rows from both tables, matching them where possible and including NULLs where there is no match.
- The WHERE clause filters the result to include only rows where either 'a.company_id' (from 'company') or 'b.company_id' (from 'foods') is NULL. This means it includes rows where there is no corresponding company in the 'company' table or no corresponding food in the 'foods' table.
- The ORDER BY clause sorts the result by the 'company_name' column in ascending order.
- This query is useful for identifying companies or foods that do not have matches in the other table, providing insight into missing relationships or data integrity issues.
Output:
a.ComID C_Name b.ComID I_Name
---------- ------------------------- ---------- ---------------
19 sip-n-Bite.
Salt n Shake
Full Outer Join using Union clause
A UNION clause can be used as an alternate to get the same result as FULL OUTER JOIN
Here is the example:
SQL Code:
-- Selecting specific columns from 'table_A' (aliased as 'table_a') and 'table_B' (aliased as 'table_b')
SELECT table_a.A, table_a.M, table_b.A, table_b.N
-- Performing a FULL OUTER JOIN between 'table_A' and 'table_B'
FROM table_A
FULL OUTER JOIN table_B
-- Matching rows from 'table_A' and 'table_B' where the values in column 'A' are equal
ON table_A.a = table_b.A
-- Sorting the result by the 'A' column from 'table_A' in ascending order
ORDER BY table_A.A;
Explanation:
- This SQL query is retrieving data from two tables: 'table_A' (aliased as 'table_a') and 'table_B' (aliased as 'table_b').
- It selects specific columns from these tables: 'A' and 'M' from 'table_A', and 'A' and 'N' from 'table_B'.
- The query performs a FULL OUTER JOIN between 'table_A' and 'table_B'.
- The join condition is specified in the ON clause, which matches rows based on the equality of values in column 'A' between the two tables.
- A FULL OUTER JOIN returns all rows from both tables, matching them where possible and including NULLs where there is no match.
- The ORDER BY clause sorts the result by the 'A' column from 'table_A' in ascending order.
- This query is useful for combining data from 'table_A' and 'table_B' while ensuring that no data is lost, as it includes both matched and unmatched rows from both tables, sorted by the values in column 'A' from 'table_A'.
FULL OUTER JOIN using LEFT and RIGHT OUTER JOIN and UNION clause
The following code is, the combination of LEFT OUTER JOIN and RIGHT OUTER JOIN and combined by, using UNION clause
SQL Code:
-- Selecting specific columns from 'table_A' (aliased as 'table_a') and 'table_B' (aliased as 'table_b') for the LEFT JOIN
SELECT table_a.A, table_a.M, table_b.A, table_b.N
-- Performing a LEFT OUTER JOIN between 'table_A' and 'table_B'
FROM table_A
LEFT OUTER JOIN table_B
-- Matching rows from 'table_A' and 'table_B' where the values in column 'A' are equal
ON table_A.a = table_b.A
-- Combining the result set with the result of the next SELECT statement using UNION
UNION
-- Selecting specific columns from 'table_A' (aliased as 'table_a') and 'table_B' (aliased as 'table_b') for the RIGHT JOIN
SELECT table_a.A, table_a.M, table_b.A, table_b.N
-- Performing a RIGHT OUTER JOIN between 'table_A' and 'table_B'
FROM table_A
RIGHT OUTER JOIN table_B
-- Matching rows from 'table_A' and 'table_B' where the values in column 'A' are equal
ON table_A.a = table_b.A;
Explanation:
- This SQL query combines the results of a LEFT OUTER JOIN and a RIGHT OUTER JOIN using UNION.
- The LEFT OUTER JOIN retrieves all rows from 'table_A' and the matching rows from 'table_B', where the values in column 'A' are equal.
- The RIGHT OUTER JOIN retrieves all rows from 'table_B' and the matching rows from 'table_A', where the values in column 'A' are equal.
- The UNION operator combines the results of the two SELECT statements, removing duplicates.
- Each SELECT statement selects specific columns from 'table_A' and 'table_B', aliased as 'table_a' and 'table_b' respectively, for clarity.
- This query is useful for combining data from 'table_A' and 'table_B' using both LEFT and RIGHT OUTER JOINs, ensuring that all rows from both tables are included in the result set.
Note: Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition.
Comparison with Other SQL Joins
| Join Type | Description | Result Set Inclusion |
|---|---|---|
| INNER JOIN | Returns only the rows with matching values in both tables | Matched rows only |
| LEFT JOIN | Returns all rows from the left table and matched rows from the right table | All rows from the left table; matched rows from the right |
| RIGHT JOIN | Returns all rows from the right table and matched rows from the left table | All rows from the right table; matched rows from the left |
| FULL OUTER JOIN | Returns all rows from both tables, with NULLs where there is no match | All rows from both tables |
Example:
Assume, we have two tables: 'Customers' and 'Orders'.
- INNER JOIN returns only customers who have placed orders.
- LEFT JOIN returns all customers, and their orders if any.
- RIGHT JOIN returns all orders, and the customers who placed them.
- FULL OUTER JOIN returns all customers and all orders, matching them where possible.
Advanced Techniques with Full Outer Join
Using Common Table Expressions (CTEs) with FULL OUTER JOIN
CTEs can simplify complex queries by breaking them into manageable parts. Assume, we have two tables: 'Sales' and 'Returns'.
SQL Code:
WITH CTE_Sales AS (
SELECT TransactionID, ProductID, Quantity
FROM Sales
),
CTE_Returns AS (
SELECT ReturnID, ProductID, ReturnDate
FROM Returns
)
SELECT CTE_Sales.TransactionID, CTE_Sales.ProductID, CTE_Sales.Quantity, CTE_Returns.ReturnID, CTE_Returns.ReturnDate
FROM CTE_Sales
FULL OUTER JOIN CTE_Returns
ON CTE_Sales.ProductID = CTE_Returns.ProductID;
Explanation:
- This query uses CTEs to create temporary result sets for sales and returns, which are then joined using FULL OUTER JOIN.
Combining FULL OUTER JOIN with Aggregations
Imagine you have two tables: `Sales` and `Returns`.
SQL Code:
SELECT COALESCE(Sales.ProductID, Returns.ProductID) AS ProductID,
SUM(Sales.Quantity) AS TotalSales,
COUNT(Returns.ReturnID) AS ReturnCount
FROM Sales
FULL OUTER JOIN Returns
ON Sales.ProductID = Returns.ProductID
GROUP BY COALESCE(Sales.ProductID, Returns.ProductID);
Explanation:
- This query aggregates sales and return data, showing total sales and the number of returns for each product.
Frequently Asked Questions (FAQ) - SQL FULL OUTER JOIN
1. What is a FULL OUTER JOIN in SQL?
A FULL OUTER JOIN is a type of SQL join that returns all rows from both joined tables, including matched and unmatched rows. Matched rows from both tables are combined, while unmatched rows from each table are included with NULLs filling in where there are no corresponding matches.
2. When should we use a SQL FULL OUTER JOIN?
We should use a FULL OUTER JOIN when we need to merge data from two tables and want to ensure that no data from either table is excluded, even if there are no matching rows between them. This is useful for comprehensive data analysis and reporting.
3. How does a SQL FULL OUTER JOIN differ from other joins?
- INNER JOIN: Returns only rows with matching values in both tables.
- LEFT JOIN: Returns all rows from the left table and matched rows from the right table.
- RIGHT JOIN: Returns all rows from the right table and matched rows from the left table.
- FULL OUTER JOIN: Returns all rows from both tables, with NULLs where there are no matches.
4. What are common use cases for a SQL FULL OUTER JOIN?
- Data Reconciliation: Combining financial or other records from different sources to ensure no data is overlooked.
- Inventory Management: Merging product lists from various suppliers to get a full inventory view.
- Customer Data Integration: Integrating customer information from multiple regions or systems where not all customers are present in each dataset.
5. What are some potential pitfalls when using SQL FULL OUTER JOIN?
- Large Result Sets: FULL OUTER JOINs can produce very large result sets, especially if both tables have many rows, leading to high memory usage and slower performance.
- NULL Handling: Since FULL OUTER JOINs include unmatched rows with NULLs, careful handling is required to avoid issues in subsequent calculations and data processing.
6. How can we optimize the performance of a SQL FULL OUTER JOIN?
- Limit Columns: Select only the columns you need rather than using SELECT * to reduce the amount of processed data.
- Filter Early: Apply WHERE clauses as early as possible to reduce the size of the data being joined.
- Indexing: Ensure that columns used in the join condition are indexed to speed up the join process.
7. Can we use a SQL FULL OUTER JOIN with a WHERE clause?
Yes, we can use a WHERE clause with a FULL OUTER JOIN to filter the results further. For example, we can filter out rows where there are no matches in either table or focus on specific conditions within the joined data.
8. How does a SQL FULL OUTER JOIN work with NULL values?
In a FULL OUTER JOIN, unmatched rows from either table will have NULL values for the columns of the table with which there was no match. This can affect calculations and aggregations, so NULL handling is crucial.
9. Can we simulate a SQL FULL OUTER JOIN using other joins?
Yes, a FULL OUTER JOIN can be simulated using a combination of LEFT JOIN and RIGHT JOIN with a UNION clause. This method combines the results of both joins to include all rows from both tables.
10. What are some advanced techniques to use with SQL FULL OUTER JOIN?
- Common Table Expressions (CTEs): Use CTEs to simplify complex queries by breaking them into manageable parts before performing a FULL OUTER JOIN.
- Aggregations: Combine FULL OUTER JOIN with aggregate functions to summarize data across both tables.
Click on the following to get the slides presentation of all JOINS -
Here is a new document which is a collection of questions with short and simple answers, useful for learning SQL as well as for interviews.
Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.
Previous: SQL RIGHT JOIN
Next: Join a table to itself