Full Outer Join

What is mean by Full Outer Join examples?

  • 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.

CLICK HERE TO GET INFORMATION ON INNER JOIN AND OUTER JOIN

  • 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 aFull Outer Joinpplications

  • Syntax:

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

2 Replies to “What is mean by Full Outer Join examples?”

Comments are closed.