Full Outer Join | Cartesian Join-
The Full Outer Join and Cartisian joins are less used joins in day to day applications,reporting,web applications.These joins are less used because it deals with all the data from left table as well as right table.So if we want all the records from both left and right table we will directly use table no need to use the joins.
- Full Outer Join:
When 2 tables are connected with each other such that it should take all records from left table as well as right table and only matching record from both the tables then this join is called as Full Outer Join.This join is less used join in applications.
Full outer join is one of the less used join in day to day applications
Select t1.col1,t2.col2….t ‘n’col ‘n.’.
from table1 t1 full join table2 t2
- Real Life Scenario:
Question: What is the query to display the all departments with its Employee name allocation?
Consider there are 2 tables.Employee table which has following columns:
Employee_num Employee_name Department ID Salary 1 Amit 100 680000 2 Rohan 100 550000 3 Rohit 101 430000
There is another table called as Depatment which has following structure:
Department ID Department Name 100 OBIEE 101 Oracle PLSQL 102 COGNOS
- SQL Query:
Select a.Department_ID,b.Employee_Name from
Employee b full outer join Department a
Department ID Department Name 100 Amit 100 Rohan 101 Rohit 102 Null
We have used the Full Outer join here because we need to show all the records from all the Tables.
This join is very less used join in day to day application.Developers have strict instructions that join should not be Cartesian product.Becase if we use this join then each and every record from first table will join to each and every record of second table.When we are not giving any joining condition then it displays Cartesian product.
Hope You get the idea about Full outer join and cartesian join in SQL.