Full Outer Join in SQL

Introduction

In the world of SQL, data retrieval is all about understanding the relationships between distinct data sets. Among the various types of joins, the Full Outer Join is a powerful tool that lets you to combine rows from two or more tables, regardless of whether a match exists. This article will go into the intricacies of the Full Outer Join, equipping you with the knowledge you need to maximise its potential.

Overview

The Full Outer Join is a type of join that returns all records when there is a match in either the left or right table. This join can be invaluable when you need a complete set of records from both tables, with NULLs in place where a record from one table has no corresponding match in the other.

What is Full outer join in SQL?

The FULL OUTER JOIN is a powerful operation that allows you to combine data from two tables, ensuring that you get all records from both tables, regardless of whether there’s a match or not. Here’s how it works:

• Matching Rows: When you perform a FULL OUTER JOIN, the resulting dataset includes all rows from both the left (table 1) and right (table 2) tables where there’s a match based on the specified join condition. If a row in one table has a corresponding match in the other table, it appears in the result set.
  

• Non-Matching Rows: Importantly, the FULL OUTER JOIN also includes rows from both tables where there’s no match. If rows in the left table don’t have corresponding matches in the right table (and vice versa), those rows still appear in the result set. The columns from the non-matching side are filled with NULL values.
  

Full Outer Join Syntax

The basic syntax for a Full outer join in SQL is as follows:

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

• SELECT: This keyword specifies the columns you want to retrieve from the joined tables. You can list multiple columns separated by commas.
• FROM: Indicates the source tables for the join operation. In your example, table1 and table2 are the tables being joined.
• FULL OUTER JOIN: This type of join highlights all records when there is a match in either the left (table1) or right (table2) table records. It combines the results of both the inner join and the left outer join. The FULL JOIN keyword is equivalent to FULL OUTER JOIN.
• ON: Specifies the condition for joining the tables. You compare the values of a specific column from table1 with the corresponding column in table2.

Here’s an example using the Northwind sample database:

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;

In this example:

• We retrieve the CustomerName and OrderID columns.
• The FULL OUTER JOIN ensures that all matching records from both tables are included, even if there are rows in “Customers” without matches in “Orders” or vice versa.
  

Examples

Let us consider two tables, namely Customers and Orders, so that we can understand the full join examples in detail:-

Customers

Orders

• Full Outer Join with WHERE clause

Even with a Full Outer Join, you can filter the results using a WHERE clause. Here’s how:

SELECT *
FROM Customers
FULL OUTER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
WHERE Customers.CustomerName = 'John Doe';

Output

Output:

CustomerId CustomerName OrderID CustomerId OrderAmount

----------- -------------- --------- ----------- ------------

1 John Doe 101 1 250

1 John Doe 103 1 150

Explanation

In this scenario, we are filtering the results to only show rows related to "John Doe". Since "John Doe" has a matching order, we see that order in the output.

• Full Outer Join with AS Alias

Using aliases (AS keywords) can make your queries more readable, especially with longer table names:

SELECT C.CustomerName, O.OrderAmount
FROM Customers AS C
FULL OUTER JOIN Orders AS O
ON C.CustomerID = O.CustomerID;

Output

CustomerName OrderAmount

-------------- ------------

John Doe 250

John Doe 150

Jane Smith NULL

Emily Jones 450

Explanation

Here, we use aliases for table names for better readability. The AS keyword is optional. The result set includes all customers and orders, with NULL where there is no match.

• Full Outer Join using UNION Clause

In some SQL implementations that do not support Full Outer Join directly, you can emulate it using the UNION clause:

SELECT CustomerName, OrderAmount
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID

UNION

SELECT CustomerName, OrderAmount
FROM Customers
RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Output

CustomerName OrderAmount

-------------- ------------

John Doe 250

John Doe 150

Jane Smith NULL

Emily Jones 450

Explanation

The expected output would be the same as the Full Outer Join output since UNION removes duplicate rows. However, if there are customers with the same name but different IDs or orders with the same amount but different IDs, the UNION may not reflect an accurate Full Outer Join equivalent since it would remove those perceived duplicates.

• Full outer Join using LEFT and RIGHT Outer Join and using UNION clause

Here’s an alternative method using both LEFT and RIGHT joins with UNION to achieve the same result as a Full Outer Join:

SELECT CustomerName, OrderAmount
FROM Customers
LEFT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID

UNION ALL

SELECT CustomerName, OrderAmount
FROM Customers
RIGHT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE Customers.CustomerID IS NULL;

Output

CustomerName OrderAmount

---------------- -----------

John Doe 250

John Doe 150

Jane Smith NULL

Emily Jones 450

Explanation

The UNION ALL operator is used here, which does not eliminate duplicate rows. The WHERE clause in the second query ensures that only non-matching rows from the right table (Orders) are included. This avoids duplication of the rows that are already retrieved by the first query (LEFT JOIN). The result will be the same as that of a Full Outer Join.

Therefore, In all cases, the Full Outer Join provides a complete view of all records from both tables, with NULL values in the columns from the table that lack a corresponding match.

Wrapping UP

In summary, the FULL OUTER JOIN in SQL is a powerful operation that combines data from two tables, ensuring that all records from both tables are included, regardless of whether there’s a match or not. It’s particularly useful when you need a comprehensive view of your data, capturing both matching and non-matching rows. Remember to use it judiciously, as it can potentially return large result sets.

FAQ’s

Q. What is Full Outer Join in SQL?

Full Outer Join in SQL is a type of join that returns all records from both tables that are involved in the join, no matter whether there's a match between the joined columns. If there's a match, the joined table will have a single row with the combined information from both tables. If there is no match, the result set will still include a row for each non-matching record but with NULL values for the columns from the table that lack a matching row.

Q. What is Full Outer Inner Join in SQL?

The term "Full Outer Inner Join" is not a standard SQL join type and seems to be a combination of terms. In SQL, we have either a "Full Outer Join" or an "Inner Join," but not a combination of both. An Inner Join only returns the rows that have matching values in both tables, contrasting with a Full Outer Join, which returns all rows from both tables.

Q. What is a Full Outer Join Equivalent to in SQL?

In SQL, a Full Outer Join is equivalent to the union of a Left Outer Join and a Right Outer Join. This means it includes all records from both the left and the right tables, and it fills in NULL values for matches that do not exist on either side.

Q. What is Union and Full Outer Join in SQL?

A UNION in SQL combines result sets of two or more SELECT statements into a single result set, including all the rows from the queries and eliminating duplicates. The Full Outer Join, on the other hand, is used to combine all rows from two joined tables, not just the ones that match.

Q. What Does a Full Outer Join Do?

A Full Outer Join effectively merges the content of two tables into one result set. It includes all rows from both tables and when there are no matches in one table, it shows NULL values for the missing columns. It is particularly useful when you need to maintain all information from both tables without losing any data due to non-matching rows.

Q. What is Full Join with Example?

A Full Join, also known as Full Outer Join, can be illustrated with the following example:

Let's say we have two tables, Customers and Orders. We want to see a list of all customers and their orders, as well as all orders and the customers who made them, regardless of whether every customer has an order or if there are orders with no corresponding customer.

Here's a simple representation of the tables:

Customers

Orders

A Full Outer Join SQL query would look like this:

SELECT Customers.CustomerName, Orders.OrderAmount
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID =
Orders.CustomerID;

The result set would look something like this:

This result set includes all customers and all orders. Note that "Jane Smith" didn't place any order, and there's an order from a customer (CustomerID = 4) not listed in our Customers table.