SQL FULL OUTER JOIN

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

Syntax:

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:

Now, we will create Department table.

Now, we are going to insert values into it.

The Department table will look like:

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

Output:

Example 2:

Consider below two tables Student and Student Joining.

Table: Student

Table: Student_joining

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

Output:

Example 3:

Let us suppose there are 2 tables product and product_category.

Table: product

Table: product_category

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

The result set will be

Output:

FULL OUTER JOIN With WHERE Clause

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.

Table: employee

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

Output:

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.