Join related query

“JOIN” is an SQL keyword used to query data from two or more related tables.

Types of join:-
(i) Inner Join
(ii) Outer Join
(a) Left Outer Join(Left Join) (b) Right Outer Join(Right Join)
(iii) Self Join

emp_location

Example:- In the above figure, it has two tables, (i) Employee (ii) location

(i) Inner Join:- It require that a row from the first table has a match in the second table based on the join conditions.
OR
An inner join of two tables gives the result of intersect of two tables.

Ques:- Please write a query fetch employee name with location record?
mysql->SELECT e.emp_name,l.location_name from employee e inner join location l on e.emp_id=l.emp_id
inner-join-output

(i) Left Outer Join:- It returns all rows from the left table (employee), with the matching rows in the right table (location). The result is NULL in the right side when there is no match.

mysql->SELECT e.emp_name,l.location_name from employee e left join location l on e.emp_id=l.emp_id
left-join

(i) Right Outer Join:- It returns all rows from the right table (location), with the matching rows in the left table (employee). The result is NULL in the left side when there is no match.
mysql->SELECT e.emp_name,l.location_name from employee e right join location l on e.emp_id=l.emp_id
right-join

(ii) Full Outer Join:- It returns all records in both tables regardless of any match. Where no match exists, the missing side will contain NULL.
OR
A full outer join will give you the union of employee and location table,
mysql->(SELECT e.emp_name,l.location_name from employee e left join location l on e.emp_id=l.emp_id) union (SELECT e.emp_name,l.location_name from employee e right join location l on e.emp_id=l.emp_id)
full-join

(iii) Self Join:- SELF JOIN is used to join a table to itself using join statement.
Ques:- write a query, get the employee name with employee reporting officer name?

mysql->SELECT e1.emp_name,e2.emp_name as reporting_officer_name from employee e1 left join employee e2 on e1.emp_reporting_officer=e2.emp_id
self-join