SQL OUTER JOIN: The Complete Guide

If you are new to this SQL blog, I have previously described Cross Join, Inner Join, and Left Join. In the SQL outer JOIN, both tables’ content is integrated whether they are matched or not.

When performing the inner join, rows from either table unmatched in the other table are not returned in the final result set. The unmatched rows in one or both tables can be returned in the outer join.

SQL OUTER JOIN

SQL Outer join returns all the contents of both the tables combined regardless of whether they are matched. The OUTER JOIN operator (+) is used only on one side of the join condition only.

In the outer join, unmatched rows in one or both tables can be returned. The SQL OUTER JOIN returns all the rows from the participating tables that satisfy a join condition and rows that do not meet the join criteria.

What is the use of SQL OUTER JOIN?

In SQL Outer Join, both tables’ contents are combined whether they are matched or not. In the outer join, unmatched rows in one or both tables can be returned.

Types of Outer Join

1. LEFT JOIN returns only unmatched rows from the left table.
2. RIGHT JOIN returns only unmatched rows from the right table.
3. FULL OUTER JOIN returns unmatched rows from both tables.

Consider the following table

See the following tables.

CUSTOMER:

ID	NAME	AGE	CITY
1	Rohit	20	Patna
2	Shivam	18	Jalandhar
3	Pranav	19	Dharamshala

 

ORDERS:

O_ID	Cust_ID	City	AMOUNT
201	1	Patna	3000
202	2	Jalandhar	4000
203	4	Kolkata	1000

 

Left Outer Join

This is also known as Left Join. The left outer join returns all rows in a table that is on the left side matched with the rows of the table on the right side. This concludes that the SQL left Join always contains the rows in the left table.

SYNTAX

Select column_1, column_2… from table_1 
LEFT JOIN table_2 ON CONDITION;

See the following diagram of Right Outer Join.

Let’s clear this with an example. See the following query.

Select Orders.O_ID, Customer.id,customer.name, Orders.amount 
From CUSTOMER 
LEFT JOIN Orders ON Customer.ID = Orders.Cust_ID;

See the output.

EXPLANATION

As you can see, all the contents of the left table are displayed, whether it is matched with the right table or not. Right table contents that are matched with the left table are displayed as well, and those that are not matched are displayed with NULL values.

Right Outer Join

SQL right join returns all the values from the rows of the right table. It also includes the matched values from the left table, but if there is no matching in both tables, it returns NULL values.

See the following syntax.

Select column_1, column_2… 
from table_1 
RIGHT JOIN table_2 ON CONDITION;

See the following diagram of Right Outer Join.

Let’s see the following code example.

Select Orders.O_ID, Customer.id, customer.name, Orders.amount 
From CUSTOMER 
RIGHT JOIN Orders ON Customer.ID = Orders.Cust_ID;

See the output.

EXPLANATION

As you can see, all the contents of the right table are displayed, whether it is matched with the left table or not. Left table contents that are matched with the right table are displayed as well, and those that are not matched are displayed with NULL values.

FULL OUTER JOIN

SQL full outer join returns all the left and right tables and matching rows in both tables.

SYNTAX

Select * from table1 
FULL OUTER JOIN table2 ON CONDITION;

See the following diagram of Full Outer Join.

See the following example.

Select * from Customer 
FULL OUTER JOIN Orders ON Customer.ID = Orders.cust_id;

The above Statement will not work in MySQL, Because SQL’s full outer join returns a result set that combines both SQL left join and SQL right join.

So to generate the result, we have to use the UNION operator.

Now, see the following statement.

Select * from Customer 
LEFT JOIN Orders ON Customer.id = Orders.Cust_ID 
UNION 
Select * from Customer 
RIGHT JOIN Orders 
ON Customer.id = Orders.Cust_id;

See the following output.

Explanation

As this is a full outer join, all rows from both tables, whether matched or not, are included in the output.

The FULL OUTER JOIN returns all the matching records from both the tables whether the other table matches.

So, if there are rows in the “Customers” table that do not have matches in “Orders” table or if there are rows in the “Orders” that do not have matches in the “Customers” table, those rows will be in the output as well.

That’s it.