SQL Full OUTER Join, FULL JOIN, LEFT and RIGHT OUTER Join

 

OUTER JOIN:

As we know in the normal joining operation we can extract the data from two tables by forming pairs of related rows where matching columns of two different tables have the same value. Simple joining doesn’t extract the rows of both tables that don’t match. But if we want to extract matching and non-matching rows then we can use outer join for that purpose. Outer join is the type of join that extends the results of simple join by returning non-matching rows as well.

There are three types of OUTER Join. These are

Common syntax of ALL Types Of OUTER JOIN:

SELECT Table_Name1.ColumnN, Table_Name2.ColumnN
FROM table1

INNER/LEFT/RIGHT/FULL JOIN table2

ON table1.id = table2.matching_id

WHERE condition(s)

Full Join/Full Outer join:

FULL OUTER join and FULL Join are the same. In the full outer join when we join the tables it will return common or matching rows as well as those rows that doesn’t match in both tables.Let’s see an example to clarify this:

Let suppose we have two tables Student and Teacher:

Student Table

Teacher Table

Now we can apply outer join to extract the matching and non-matching rows of both tables

Syntax:

SELECT Table_Name1.ColumnN, Table_Name2.ColumnN
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;

Query:

Full Outer Join Query

How Full Outer join Works:

Example of Full Outer Join

This picture shows that there are some non_identical rows of both tables(that are shown in the circle of the table) and also have some identical rows that are shown in the middle intersection point of both tables. All these rows will be demonstrated in the resultant table by applying Full Outer join. If the non-matching rows are on display, then the opposite table which can be table1 or table2 that don’t have the matching row will display null corresponding to the non-matching rows in the resultant table.

Result Of FULL OUTER JOIN:

Std_ID	Std_Name	T_ID	T_Name
101	Smith	null	null
102	Allen	1T56	David
103	julie	1T37	Aline
104	Clark	null	null
105	john	null	null
106	null	1T34	Leorin
107	null	1T78	Zoha

LEFT Join/LEFT OUTER Join:

Left Outer join is used to perform the joining of two tables and return obviously the matching rows as well as non_identical rows of a left table which is table1 i.e Student table according to the above example. So, if we apply LEFT OUTER Join on Student and Teacher table then it will return the identical rows of both tables plus add the non-identical rows from the LEFT/table1 i.e Student table. Moreover, this will display NULL in the table2/Teacher table’s field where there is no data corresponding to the non_matching rows of table1i.e Student table.

Syntax of LEFT OUTER Join:

SELECT Table_Name1.ColumnN, Table_Name2.ColumnN
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

Query of LEFT OUTER Join:

How Left Outer Join Works:

Example of Left Outer Join

According to this query, it will display Only matching rows of the Right table that is Teacher table as well as all the non-matching rows of the Left table only.

Result of LEFT OUTER Join:

 RIGHT Join/RIGHT OUTER Join:

RIGHT OUTER Join is used to return all records of the Right table/table2 i.e Teacher table, and the rest of only matching records from Left table/table1 i.e Student table according to the above example. Non-matching field rows of student table will be filled with null in the resultant table.

Syntax of RIGHT OUTER Join:

SELECT Table_Name1.ColumnN, Table_Name2.ColumnN
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

Query of RIGHT OUTER Join:

How Right Outer Join Works:

Example of Right Outer Join

According to this query, it will display Only matching rows of the Left table that is the Student table However will not show the non-matching rows of the left table(Student) but will show non-matching rows of the RIGHT table(Teacher) only. that's why it is called RIGHT Outer join.

Result of RIGHT OUTER Join:

Std_ID	T_ID	T_Name	Std_Id	Std_Name
102	1T56	David	102	Allen
103	1T37	Aline	103	julie
106	1T34	Leorin	null	null
107	1T78	Zoha	null	null

Hope this article will clear the concept of Outer join and different types of Outer Join.