Full outer join is the combination of both, left outer join and right outer join.
It Returns the all values from left table as well as from right table.
Let we see the sytax of full outer join.
Syntax:
select columns_name from table1 full outer join/ full join table2 On(expression) or Using(column_name) Where(expression) order by column name;
Example:
select emp_name, dept_name from emp full outer join dept on(emp.emp_id = dept.emp_id);
Result:
Talha IT
Zubair Oracle
Salman Finance
(null) Accounts
(null) Marketing
Waqass (null)
shani (null)
To explain working, change the side of table name from the full outer join, and check the result.
Example:
select emp_name, dept_name from dept full outer join emp on(emp.emp_id = dept.emp_id);
Result:
Talha IT
Zubair Oracle
Salman Finance
shani (null)
Waqass (null)
(null) Marketing
(null) Accounts
Example:
select emp_name, dept_name from dept full outer join emp on(emp.emp_id = dept.dept_id);
Result:
Talha IT
Zubair Oracle
Salman Finance
shani Accounts
Waqass Marketing
Example with using clause:
select emp_name, dept_name from dept full outer join emp using(emp_id);
Result:
Talha IT
Zubair Oracle
Salman Finance
shani (null)
Waqass (null)
(null) Marketing
(null) Accounts
Example with using clause and Where clause:
select emp_name, dept_name from dept full outer join emp using(emp_id) where emp_salary>2000;
Result:
Zubair Oracle
Salman Finance
shani (null)
Waqass (null)
Example with using clause and Where clause and Order by employee Name:
select emp_name, dept_name from dept full outer join emp using(emp_id) where emp_salary>2000 order by emp_name;
Result:
Salman Finance
Waqass (null)
Zubair Oracle
shani (null)
No comments:
Post a Comment