SQL JOINS :
- Sometimes we have to combine the data from so many tables.
- A join combines the data spread across tables.
- A join is performed by the 'where' clause which combines the specified rows of tables.
- A common column provides the join condition.
Types of joins :
There are three types of joins. They are
1. Simple join or inner join
2. Self join
3. Outer Join
1. Inner join
- The most important and frequently used of the joins is the inner join.
- They are also referred to as EQUIJOIN.
- Inner join retrieve only the 'MATCHED RECORDS'.
Example :
select e.employee_id,e.first_name,e.department_name,d.department_id
from employees e, departments d
where e.department_id = d.department_id;
Non-equijoin
- A join that is based on relational operators other than = is called a non-Equi join.
Example :
select a.item_id,item_name,stock,quantity
from item_master a,sales_details b
where(a.item_id=b.item_id)
and (a.stock<b.quantity);
This statement lists all items for which the stock in hand is less than the quantity ordered.
2. Outer Join
- The outer join extends the result of a simple join.
- An outer join returns all the rows from one table that do not match any row from the other table.
- The symbol + is used to represent the outer join.
Left Outer Join
- All the records from the left side table and only the matched rows join from the right side table.
Example :
select e.employee_id,e.first_name,e.department_name,d.department_id
from employees e, departments d
where e.department_id = d.department_id(+);
Right Outer Join
- All the records are from the right side table and only the matched rows left side table.
Example :
select e.employee_id,e.first_name,e.department_name,d.department_id
from employees e, departments d
where e.department_id(+) = d.department_id;
3. Self Join
- Self-join is a regular join, but the table is joined with itself.
