SQL Outer Join

In SQL, Outer Join keyword returns all rows from both the participating tables which satisfy the join condition along with rows which do not satisfy the join condition. The SQL OUTER JOIN operator (+) is used only on one side of the join condition only.

 

Outer Join is three types –

  • Left Outer Join

  • Right Outer Join

  • Full Join

We will see all type of joins in details.

 

LEFT OUTER JOIN

The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.

SQL Left Outer Join

SQL Syntax :

SELECT column_name1, column_name2
FROM table1 LEFT JOIN table2
ON table1.column_name=table2.column_name;

 

Example – Suppose we have two tables CUSTOMERS and ORDERS, In both of the tables we have the common column named CUSTOMER ID . Now select all records to form the CUSTOMERS table and matching records from the ORDERS table on the basis of Customer ID column.

SQL> SELECT Customers.CustomerName, Orders.OrderID
FROM Customers LEFT JOIN Orders
ON Customers.CustomerID=Orders.CustomerID;

 

RIGHT OUTER JOIN

The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL on the left side when there is no match.

SQL Right Outer Join

SQL Syntax :

SELECT column_name1, column_name2
FROM table1 RIGHT JOIN table2
ON table1.column_name=table2.column_name;

 

Example – Suppose we have two tables CUSTOMERS and ORDERS, In both of the tables we have the common column named CUSTOMER ID . Now select all records to form the CUSTOMERS table and matching records from the ORDERS table on the basis of Customer ID column.

SQL> SELECT Customers.CustomerName, Orders.OrderID
FROM Orders RIGHT JOIN Customers
ON Customers.CustomerID=Orders.CustomerID;

 

FULL OUTER JOIN

The FULL OUTER JOIN keyword returns all rows from the left table (table1) and from the right table (table2).

The FULL OUTER JOIN keyword combines the result of both LEFT and RIGHT joins.

SQL Full Outer Join

 

SQL Syntax :

SELECT column_name1, column_name2
FROM table1 FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;

 

 

Example – Suppose we have two tables CUSTOMERS and ORDERS, In both of the tables we have the common column named CUSTOMER ID . Now select all records to form both of the tables on the basis of Customer ID column.

SQL> SELECT Customers.CustomerName, Orders.OrderID
FROM Orders FULL OUTER JOIN Customers
ON Customers.CustomerID=Orders.CustomerID;

 

0 Comment

Leave a Reply