What is a join? Explain with example?

What is a join?

Join used to connect multiple table based on primary key & foreign key. Or join is used to combine rows from multiple tables.

What are Different Types of Join?

There are 4 different types of join.

1)      Inner join

2)      Cross join

3)      Outer join

Outer join are 3 types:

Ø  Left outer join

Ø  Right outer join

Ø  Full join

4)      Self-join

What is Inner join (Also called equi-join)?

The INNER JOIN keyword returns rows when there is at least one match in both tables. Inner join also known as Equi-join.

Example:

Select e.first_name, e.last_name, e.salary, e.department_id, d.department_name

From employees e, departments d

where e.department_id=d.department_id;

           

What is outer join?

The outer join retrieve the missing rows. The missing rows can be returned if outer join operator (+) is used in the join condition. The outer join operator (+) can be used in that side where information is missing.

The outer join are 3 types:

1)      Left outer join

2)      Right outer join

3)      Full outer join

Left Outer Join: Return all rows from the left table, even there are no matches in the right table. If information missing in the left table the outer join operator (+) is used only the left side of the equal sign ((+) =).

Example:

select e.first_name, e.last_name, e.salary, e.employee_id,  d.department_name

from employees e, departments d

where

e.department_id (+)=d.department_id;

Right Outer Join: Return all rows from the right table, even there are no matches in the left table. If information missing in the right table the outer join operator (+) is used only the right side of the equal sign (= (+)).

Example:

select e.first_name, e.last_name, e.salary, e.employee_id,  d.department_name

from employees e, departments d

where

e.department_id =d.department_id (+);

Full Outer Join: The FULL OUTER JOIN keyword returns all rows from the left table and from the right table. The FULL OUTER JOIN keyword combines the result of both LEFT and RIGHT joins.

Example:

select e.first_name, e.last_name, e.salary, e.employee_id, d.department_name

from employees e full join departments d

on e.department_id=d.department_id;

What is Cartesian or Cross join?

If there is no condition (where clause) between two tables than Cross join execute a result which is the number of rows in the first table multiplied by the number of rows in the second table and show all the result that’s called as cross join. It is also known as Cartesian product.

Example:

SELECT e.first_name, e.last_name, e.salary, e.department_id, d.department_name

FROM employees e, departments d;

OUTPUT will be 2889 rows which comes from 107*27

What is self-join (Also called nested join)?

A self-join is basically when a join is done on the same table. Whenever we make a sub query (also call nested) that means we are doing self-join.

Example:

select e.employee_id, e.first_name, e.last_name, e.salary, e.manager_id, m.first_name, m.last_name

from employees e, employees m

where e.manager_id=m.employee_id;