Full Outer Join Oracle | Full Outer Join - oracle tutorial - sql tutorial
![Oracle full outer join](https://www.wikitechy.com/tutorials/oracle/img/oracle-images/oracle-full-outer-join.gif)
Learn oracle - oracle tutorial - Oracle full outer join - oracle examples - oracle programs
What is Full outer join in oracle ?
- Full outer join returns all rows from the LEFT-hand table and RIGHT-hand table with nulls in place where the join condition is not met.
![full outer join](https://www.wikitechy.com/tutorials/oracle/img\oracle-images\full_outer_join.gif)
Syntax:
SELECT columns
FROM table1
FULL [OUTER] JOIN table2
ON table1.column = table2.column;
click below button to copy the code. By - oracle tutorial - team
Example:
SELECT wikitechy_employee.Name, Orders.OrderID
FROM wikitechy_employee
FULL OUTER JOIN Orders
ON wikitechy_employee.id=Orders.ID
click below button to copy the code. By - oracle tutorial - team
oracle tutorial , sql tutorial , sql , pl sql tutorial , oracle , pl sql , plsql
Sample Database:
- We have a table called wikitechy_employee with four fields (id, name, position, and city). It contains the following data:
![oracle full outer join table 1 output](https://www.wikitechy.com/tutorials/oracle/img\oracle-images\oracle-full-outer-join-table-1-output.png)
- We have another table called orders with three fields (id, customer_name, and orderid).
![oracle full outer join table 2 output](https://www.wikitechy.com/tutorials/oracle/img\oracle-images\oracle-full-outer-join-table-2-output.png)
Full Outer Join:
![oracle full outer join query and output](https://www.wikitechy.com/tutorials/oracle/img\oracle-images\oracle-full-outer-join-query-and-output.png)
- The FULL OUTER JOIN keyword returns all the rows from the left table (wikitechy_employee), and all the rows from the right table (Orders).
- If there are rows in "wikitechy_employee" that do not have matches in "Orders", or if there are rows in "Orders" that do not have matches in "wikitechy_employee", those rows will be listed as null.
LEFT OUTER JOIN
- Another type of join is called an Oracle LEFT OUTER JOIN.
- This type of join returns all rows from the LEFT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condition is met).
- In this visual diagram, the Oracle LEFT OUTER JOIN returns the shaded area:
![left outer join](https://www.wikitechy.com/tutorials/oracle/img\oracle-images\left_outer_join.gif)
Syntax
- The syntax for the Oracle LEFT OUTER JOIN is:
SELECT columns
FROM table1
LEFT [OUTER] JOIN table2
ON table1.column = table2.column;
click below button to copy the code. By - oracle tutorial - team
- In some databases, the LEFT OUTER JOIN keywords are replaced with LEFT JOIN.
RIGHT OUTER JOIN
- Another type of join is called an Oracle RIGHT OUTER JOIN.
- This type of join returns all rows from the RIGHT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condition is met).
- In this visual diagram, the Oracle RIGHT OUTER JOIN returns the shaded area:
![right outer join](https://www.wikitechy.com/tutorials/oracle/img\oracle-images\right_outer_join.gif)
oracle tutorial , sql tutorial , sql , pl sql tutorial , oracle , pl sql , plsql
Syntax:
- The syntax for the Oracle RIGHT OUTER JOIN is:
SELECT columns
FROM table1
RIGHT [OUTER] JOIN table2
ON table1.column = table2.column;
click below button to copy the code. By - oracle tutorial - team
- In some databases, the RIGHT OUTER JOIN keywords are replaced with RIGHT JOIN.
Oracle ALL Joins
![Oracle join query](https://www.wikitechy.com/tutorials/oracle/img\oracle-images\sql-joins-all.gif)
Oracle Join Query - Inner Join Query - Left Outer Join Query - Right Outer Join Query - Full Outer Join Query