Sunday, 20 November 2011




Outer Join - The Outer Join is a join condition in two table involving common attributes from two tables but table does not have a matching value in the other table, it is joined.

The table that does not contain the matching value is know as the Deficient table.
We have following "Emp_Table" name table:          We have following "Dept_Table" name table:

NameDept_ID
John21
Jay487
Bhavesh23
Jaimin24
Dolar24
AjayNULL


Dept_IDDept_Name
21Sales
23Engineering
24Clerical
25Marketing


Left Outer Join Example

Syntax:
SELECT Table_Name1.Column_Name,Table_Name2.Column_Name 
FROM Table_Name1,Table_Name2
WHERE Table_Name1.Column_Name (+)=Table_Name2.Column_Name;
Example Statement:
SQL> SELECT * 
FROM Emp_Table,Dept_Table
WHERE Emp_Table.Dept_ID (+)= Dept_Table.Dep_ID;
Following type write is true or posible..
SQL> SELECT * 
FROM Emp_Table LEFT OUTER JOIN Dept_Table
ON Emp_Table.Dept_ID = Dept_Table.Dep_ID;
Example Result:

Emp.Table.NameEmp_Table.Dept_IDDept_Table.Dept_NameDept_Table.Dept_ID
Jay23Engineering23
John21Sales21
Jaimin24Clerical24
Dolar24Clerical24
AjayNULLNULLNULL
Bhavesh23Engineering23


Right Outer Join Example

Syntax:
SELECT Table_Name1.Column_Name,Table_Name2.Column_Name 
FROM Table_Name1,Table_Name2
WHERE Table_Name1.Column_Name (+)=Table_Name2.Column_Name;
Example Statement:
SQL> SELECT * 
FROM Emp_Table,Dept_Table
WHERE Emp_Table.Dept_ID =(+) Dept_Table.Dep_ID;
Following type write is true or posible..
SQL> SELECT * 
FROM Emp_Table RIGHT OUTER JOIN Dept_Table
ON Emp_Table.Dept_ID = Dept_Table.Dep_ID;
Example Result:

Emp.Table.NameEmp_Table.Dept_IDDept_Table.Dept_NameDept_Table.Dept_ID
Dolar24Clerical24
Jay23Engineering23
Jaimin24Clerical24
Bhavesh23Engineering23
John21Sales21
NULLNULLMarketing25


FULL Outer Join Example

Syntax:
SELECT Table_Name1.Column_Name,Table_Name2.Column_Name 
FROM Table_Name1 FULL OUTER JOIN Table_Name2
ON Table_Name1.Column_Name =Table_Name2.Column_Name;
Example Query:
SQL> SELECT * 
FROM Emp_Table FULL OUTER JOIN Dept_Table
ON Emp_Table.Dept_ID = Dept_Table.Dep_ID;
Example Result:

Emp.Table.NameEmp_Table.Dept_IDDept_Table.Dept_NameDept_Table.Dept_ID
Dolar24Clerical24
Jay23Engineering23
Jaimin24Clerical24
AjayNULLNULLNULL
Bhavesh23Engineering23
John21Sales21
NULLNULLMarketing25

0 comments:

Post a Comment

TrainingHUB. Powered by Blogger.

Total Pageviews

THE BEST QTP TRAINING INSTITUTE IN HYDERABAD

QTP Training in hyderabad

Sql Tutorial

Popular Posts

Our Facebook Page

TrainingHUB

Followers