What is mean by Full Outer Join examples?

Select t1.col1,t2.col2….t ‘n’col ‘n.’.

from table1 t1 full join table2 t2

on t1.col=t2.col;

• 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

  on a.Department_ID=b.Department_ID;

  Output :

  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.

  Cartesian Join:

  This join is very less used join in day to day application. Developers have strict instructions that join should not be Cartesian product. Because 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.

  HOME