Javatpoint Logo
Javatpoint Logo


This article will provide you a clear explanation of the FULL OUTER JOIN operator with the help of simple and suitable examples. Before moving on to the main topic first, let us understand about FULL OUTER JOIN operator briefly.

What is FULL OUTER JOIN operator?

Full outer join is a type of join operation that merges the result sets of two tables, including both matching and non-matching rows from both tables. When both tables match the requested join condition, all rows from both tables are returned, and the columns from the non-matching table are filled up with NULL values if there is no match between the tables. The functioning of the full outer join operation is mentioned below



The basic syntax of full outer join operation is mentioned and each term in it is explained below

Here table 1 and table 2 are the two tables which are need to joined based on a specific condition.

Matching rows: The columns from both tables are included in the result set when a row from table1 matches a row from table2 according to the join condition.

Non-matching rows from table 1: If there isn't a match for a row in table 1, NULL values are utilised to fill in the columns from table 2.

Non-matching rows from table 2: If there isn't a match for a row in table 2, NULL values are utilised to fill in the columns from table 1.

Let's see few examples to understand clearly the working of full outer join operation.

Example 1:

Consider two tables Student, Department tables for demonstrating the functioning of Full Outer Join operator. First let us create these two tables and insert values into them.

Now, we are going to insert values into this table.

The Student table will look like:

ID Name Total_marks Contact_no
1 Ram 500 9127563212
2 Joseph 476 8123456782
3 Rehman 389 9814569871
4 Priyanka 450 7891234561
5 Saleem 534 7765123456
6 Dhanush 550 8877123590

Now, we will create Department table.

Now, we are going to insert values into it.

The Department table will look like:

ID department_name department_id
2 CSE 122
3 ECE 123
4 IT 126

Let's join these two tables using Full Outer Join operator.


ID Name Total_marks department_name
1 Ram 500 NULL
2 Joseph 476 CSE
3 Rehman 389 ECE
4 Priyanka 450 IT
5 Priyanka 450 NULL
6 Saleem 534 NULL

Example 2:

Consider below two tables Student and Student Joining.

Roll_no name department
134 Anish CSE
135 Rahul ECE
136 Smitha IT
137 Ganesh Mechanical
138 Arun EEE

Table: Student

Roll_no Admission_no joining_date
134 AS123 23-11-2020
136 AS126 21-08-2021
137 AS127 21-08-2021

Table: Student_joining

Now, we will join these two tables using Full Outer join operator.


Roll_no name Admission_no Joining_date
134 Anish AS123 23-11-2020
135 Rahul NULL NULL
136 Smitha AS126 21-08-2021
137 Ganesh AS127 21-08-2021
138 Arun NULL NULL

Example 3:

Let us suppose there are 2 tables product and product_category.

product_id product_name category_id
1 Laptop 22
2 Shirt 24
3 Jeans 24
4 Mobile 22
5 Book 25

Table: product

product_id category
2 Clothing
5 Stationary

Table: product_category

Let us combine these two tables using FULL OUTER JOIN operator on basis of product_id.

The result set will be


product_id product_name category
1 Laptop NULL
2 Shirt Clothing
3 Jeans NULL
4 Mobile NULL
5 Book Stationary


In the above examples we have joined two tables by using ON clause only, but the records tables can be filtered with the help of WHERE clause i.e, FULL OUTER JOIN can be used along with WHERE Clause to join data based on a specific condition.

Syntax for FULL OUTER JOIN operator along with WHERE clause is provided below:

Observe below example to clear understand how tables can be joined on a specific condition by using WHERE clause along with FULL OUTER JOIN operator.

Example 4:

Consider below two tables employee and department to understand how two tables can be joined based on a specific condition.

emp_id emp_name salary
45 Ramesh 22000
46 Rahim 20000
47 Sruthi 30000
48 Swetha 35000
50 Priyanka 50000
51 Anjali 40000

Table: employee

emp_id department_name department_id
45 HR 33
47 Development 35
48 marketing 36
51 R&D 39

Table: department

Now, we are going to execute below command to join above two tables based on condition where salary of employee should be greater than 25000.

The result set will look like


emp_id emp_name salary department_name
47 Sruthi 30000 Development
48 Swetha 35000 marketing
51 Anjali 40000 R&D

So, this all about the working of FULL OUTER JOIN operation. This article has provided clear explanation of FULL OUTER JOIN operator with valid and suitable examples.

Next TopicSQL NOT IN

Youtube For Videos Join Our Youtube Channel: Join Now


Help Others, Please Share

facebook twitter pinterest

Learn Latest Tutorials


Trending Technologies

B.Tech / MCA