SQL Full Outer Join

Last update on April 13 2024 08:13:31 (UTC/GMT +8 hours)

What is Full Outer Join in SQL?

In SQL the FULL OUTER JOIN combines the results of both left and right outer joins and returns all (matched or unmatched) rows from the tables on both sides of the join clause.

Pictorial Presentation: SQL Full Outer Join

Syntax:

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

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: SQL Full Outer Join between two tables

Here is an example of full outer join in SQL between two tables.

Sample table: foods

Sample table: company

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.

Pictorial Presentation SQL Full Outer Join:

Sql full outer join with left and right 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.


-- 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:

Here is the SQL statement:


-- 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


-- 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.

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