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 records 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 records 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.

Join SQL:

Inner Join:

Select e.employee_id, e.first_name, e.salary, d.department_id, d.department_name from employees e inner join departments d on e.department_id = d.department_id;

select e.employee_id, e.first_name || ' ' || e.last_name, d.department_name from employees e inner join departments d on e.department_id = d.department_id;

Left Outer Join:

Select e.employee_id, e.first_name, e.salary, d.department_id, d.department_name from employees e left outer join departments d on e.department_id = d.department_id;

Right Outer Join:

Select e.employee_id, e.first_name, e.salary, d.department_id, d.department_name from departments d right outer join employees e on e.department_id = d.department_id;

Both will show the same things.

In the first query I have written employees table in the left side of the inner join. So the join will consider employees as the left table and departments as right table. So left outer join will show all the records from left table (employees) and only the matching records from the right table (department).

In the second query I have written departments table in the left side of the inner join. So the join will consider departments as the left table and employees as right table. So right outer join will show all the records from right table (employees) and only the matching records from the left table (department).

So they both will show the same thing.

Select e.employee_id, e.first_name, e.salary, e.department_id, d.department_name from employees e right outer join departments d on e.department_id = d.department_id;

This query will display only the matching records in the first_name, salary and department_id field and all the records of the department_name field.

Full Join:

Select e.employee_id, e.first_name, e.salary, d.department_id, d.department_name from employees e full join departments d on e.department_id = d.department_id;

This will display all the records from both the table.

Triple Join:

select e.employee_id, e.first_name || ' ' || e.last_name name, e.salary, l.city from employees e inner join departments d on e.department_id=d.department_id inner join locations l on d.location_id = l.location_id;

Another way:

select e.employee_id, e.first_name || ' ' || e.last_name name, e.salary, l.city from employees e, departments d, locations l where e.department_id=d.department_id and d.location_id = l.location_id;