OUTER JOIN

It is the extension of Inner Join operation because Inner selects only matched records from multiple tables where Outer Join selects matched records as well as unmatched records. It includes

a). Left Outer Join
b). Right Outer Join
c). Full Outer Join

Left Outer Join:It selects matched records from both the tables as well as unmatched records from Left side table. For doing this operation we have to keep a special symbol
'*' at the left side of the equality condition.

Syntax: SELECT TABLE1.COLUMN1,TABLE1.COLUMN2,.....TABLE2.COLUMN1,TABLE2.COLUMN2,.... FROM TABLE1.TABLE2 WHERE TABLE1.COMMON COLUMN *=TABLE2.COMMON COLUMN

Right Outer Join
: It selects matched records from both the tables as well as unmatched records from Right side table. For doing this operation we have to keep a special symbol '*' at the right side of the equality condition.

Syntax: SELECT TABLE1.COLUMN1,TABLE1.COLUMN2,.....TABLE2.COLUMN1,TABLE2.COLUMN2,.... FROM TABLE1.TABLE2 WHERE TABLE1.COMMON COLUMN =* TABLE2.COMMON COLUMN

Full Outer Join: It is just combination of Left Join + Right Join. It selects matched records as well as unmatched records from the given tables. For Full Outer Join we have to follow a special syntax called ANSI SQL Syntax.

ANSI SQL Syntax:

SELECT TABLE1.COLUMN1,TABLE1.COLUMN2,.....TABLE2.COLUMN1,TABLE2.COLUMN2,........FROM TABLE1 FULL OUTER JOIN TABLE2 ON TABLE1.COMMON COLUMN=TABLE2.COMMON COLUMN
Share/Bookmark

No comments: