SQL-Server-Outer-Join .

In this session i have discussed the SQL Server Outer-Join .

Outer Join

There are three types of outer join given below .

a)Left Outer Join

b)Right Outer Join

c)Full Outer Join

a)Left Outer Join - It is used to retrieve all the records from left table(tbl1) and matches records from right table(tbl2) if there are no match then it will return null .

b)Right Outer Join - It is used to retrieve all the records from right table(tbl2) and matches records from left table(tbl1) if there are no match it will return null .

Full Outer Join - It is combination of left and right join . It will retrieve all the records if there is match with either left or right table .


Outer Join syntax

a)Left Outer Join Syntax

    SELECT column_names  FROM tbl1
    LEFT OUTER JOIN tbl2
    ON
    tbl1.column_name=tbl2.colum_name
    

b)Right Outer Join Syntax

    SELECT column_names  FROM tbl1
    RIGHT OUTER JOIN tbl2
    ON
    tbl1.column_name=tbl2.colum_name
    

c)Full Outer Join Syntax

    SELECT column_names  FROM tbl1
    FULL OUTER JOIN tbl2
    ON
    tbl1.column_name=tbl2.colum_name
    


Database Demo

The following data shown by Flight table of abc database .

Flight_Code Source Destination
A001 Delhi Kolkata
A002 Delhi Russia
A003 Mumbai USA
A004 Mumbai Australia

The below is Reservation table of abc database .

Flight_Code NO_Of_Seat
A001 100
A002 60
A003 50

Left Outer Join Example .

The below sql left outer join code display all the data from left table(Flight) and matching data from right table(Reservation) . It will match with common column from the Flight and Reservation table . Flight_Code is common in both table .

SELECT Flight.Flight_Code , Flight.Source , Flight.Destination ,
Reservation.No_Of_Seat FROM Flight
LEFT OUTER JOIN Reservation
ON
Flight.Flight_Code=Reservation.Flight_Code
    

The output is look like below .

Flight_Code Source Destination No_Of_Seat
A001 Delhi Kolkata 100
A002 Delhi Russia 60
A003 Mumbai USA 50
A004 Mumbai Australia NULL

In the above code the A004 flight_code of Flight table(left tbl) not match with Reservation table(right tbl) flight_Code column so it will display the null .

Right Outer Join Example .

The below sql right outer join code display all the data from right table(Reservation) and matching data from left table(Flight) . It will match with common column from the Flight and Reservation table . Flight_Code is common in both table .

SELECT Flight.Flight_Code , Flight.Source , Flight.Destination ,
Reservation.No_Of_Seat FROM Flight
Right OUTER JOIN Reservation
ON
Flight.Flight_Code=Reservation.Flight_Code
    

The output is look like below .

Flight_Code Source Destination No_Of_Seat
A001 Delhi Kolkata 100
A002 Delhi Russia 60
A003 Mumbai USA 50

In the above code all the Flight_Code of Reservation table(right table) match with Flight_Code of Flight table(left table) .

Full Outer Join Example .

The below sql full outer join code display all the data if there is match with either left or right table .

SELECT Flight.Flight_Code , Flight.Source , Flight.Destination ,
Reservation.No_Of_Seat FROM Flight
FULL OUTER JOIN Reservation
ON
Flight.Flight_Code=Reservation.Flight_Code
    

Welcome to AndroidATPoint.

back image                     next image