In this lesson we are going to study about Outer Join in a database.
OUTER JOIN:
In Outer join operation ,all records from the source table are included in the result even though they did't satisfy the join condition .
SELECT Columns Names from both Tables FROM tablenameA
LEFT | RIGHT | FULLOUTER JOIN tablenameB ON condition
Left outer join :
Related Article
OUTER JOIN:
In Outer join operation ,all records from the source table are included in the result even though they did't satisfy the join condition .
TYPE OF OUTER JOIN:
- Left outer join .
- Right outer join .
- Full outer join .
SYNTEX:
SELECT Columns Names from both Tables FROM tablenameA
LEFT | RIGHT | FULLOUTER JOIN tablenameB ON condition
Left outer join :
- Left outer join return the unmatched rows from the left table with matched rows from the right table.
- It selects all the matched rows from the tableB and unmatched rows from tableA.
- Specify the outer join operator in the left side table.
Right outer join :
- Return the unmatched rows from the right tableB plus matched rows from the left tableA.
- Specify the outer join operator in the right side table.
Full outer join :
- It is a combination of left and right outer join .
- Join operation combines the results of both left and right outer join .
- The joined table will contain all the records from both tables.
- Fill the values NULL for missing matches on either side .
Example:
SQL :
from employees emp
left outer join departments dept
on
emp.department_id=dept.department_id;
from employees emp
right outer join departments dept
on
emp.department_id=dept.department_id;
SQL :
- Left outer join
from employees emp
left outer join departments dept
on
emp.department_id=dept.department_id;
- Right outer join
from employees emp
right outer join departments dept
on
emp.department_id=dept.department_id;
- Full outer join
select emp.employee_id,emp.last_name,dept.department_id
from employees emp
full outer join departments dept
on
emp.department_id=dept.department_id;
from employees emp
full outer join departments dept
on
emp.department_id=dept.department_id;
Related Article
- Joins
- Cross Join
- Equi Join
- Non Equi Join
- Self Join
- Natural Join
- Join with Using clause.
- Join with ON clause
No comments:
Post a Comment