SQL FULL OUTER JOIN

What is SQL FULL OUTER JOIN

The SQL FULL OUTER JOIN is a type of outer join that combines all the rows from the left table and right table. It add all the matching rows from both the tables. If there is no match in both the table, it return null value for the missing cell.

In order to get the full outer join output in SQL, the query result finds all the rows from the first table and the second table and put all the matching rows from the these table.

One thing you should note here that if there is no matching values in both the tables it returns a null value.

How to Use FULL OUTER JOIN in SQL

Now, if you want to understand the how to use full outer join in SQL, you have to use the two tables and the syntax given below. Both the tables are required from where you have to take all the rows for the matching columns.

Syntax

The above syntax result out all the rows from both the tables in the SQL query. It gives you output for all the matching column rows from both the table.

It also finds non-matching value and returns out null value.

Example of SQL RIGHT OUTER JOIN

Now, let us create the two tables for Purchaser and Seller as given below to understand the example of full outer join in SQL server.

Below are the examples that contains the statement and the output of the full outer join of the given tables.

SQL FULL OUTER Join Example Using the Select Statement

There are two tables, the first table is Purchaser table and second is the Seller table.

The first table contains the list of the purchasers.
Table 1: Purchaser

Purchaser_ID	Purchaser_Name	Plot_No	Service_Id
1	Sam	12	1001
2	Pill	13	1002
3	Don	14	1003
4	Brock	15	1004

The second table contains the list of sellers.
Table 2: Seller

Id	Seller_Name	Seller_Email
1001	Big Show	[email protected]
1002	Gem	[email protected]
1003	Matt	[email protected]

Now, let’s combine the above two tables using the example given below.

Example

Output

Service_Id	Seller_Name	Purchaser_Name	Plot_No
1001	Big Show	Sam	12
1002	Gem	Pill	13
1003	Matt	Don	14
1004	Null	Brock	15

You must also read.

• SQL INNER JOIN statement
• SQL LEFT OUTER JOIN statement
• SQL RIGHT OUTER JOIN statement

Reference
Outer Join