15. SQL(Join)

Join:

Inner Join or equi join:

2 tables there is a common field dept_id. Joining them is called inner join or equi join. If values are common between 2 tables then only those common values will be displayed in inner join.

Outer Join:- 3 types:

i. Left outer Join: In left outer join it will only all the fields from the left table and only the common field between the tables.

ii. Right Outer join: In right outer join it will only all the fields from the right table and only the common field between the tables.

iii. Full join: In full join all the values will be displayed.

Employee

Department

For example with the above table

Inner join: Display:

Only the records containing the common values in dept_id field 20, 30, 50, 60 and 80 are displayed in both tables.

Left Outer join: Display:

The records containing the common values in dept_id field in both tables are 20, 30, 50, 60 and 80. Also 110 and 120 are there in left table but not in right table. In left outer join all the records of the left table are displayed and only the matching values of the right table are displayed. So 20, 30, 50, 60, 80, 110, 120 are displayed.

Right Outer join: Display:

The records containing the common values in dept_id field in both tables are 20, 30, 50, 60 and 80. Also 10 and 100 are there in right table but not in left table. In right outer join all the records of the right table are displayed and only the matching values of the left table are displayed. So 10, 20, 30, 50, 60, 80, 100 are displayed.

Full join: Display:

In full join all records are displayed. So all the records, 10, 20, 30, 50, 60, 80, 100, 110, 120 all are displayed.