SQL Joins

If you want to fetch multiple data types of data from multiple tables we can write joins. Joins combines two or more tables. 
There are different types of joins like left, right, inner, outer, full join.

Self-join :-
A Table which is joined by itself is know as self-join. 
Alias names like E1,E2 are given as prefix to column with condition to impliment selfjoin.

(select e1.eno, e2.ename, e1.dno from emp e1, emp e2 where e1.eno = e1.eno) 
                                           or      
(select d1.dno, d2.dname from dept d1, dept d2 where d1.dno=d2.dno)

Result: 

Equi-join :-
Where equal to operator is used. or we select both the table and give condition between both tables.

(select * from emp, dept where dept.dno = emp.dno;)
                                  or
(SELECT * FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO;) 

Result: 

Non equi join :-
Where equal to operator is not used in the query it is called as Non equi join.

( select * from emp, dept where dept.dno > emp.dno ).

Result: 

Natural join :- 
It return only matching columns

(select * from emp natural join dept)

Result:

Cross Join :-
It multiplies first table by the number of rows in the second table.
(select * from emp cross join dept)

Result:

Inner Join :-
It returns matching rows from both the tables.

(SELECT * FROM EMP 

INNER JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO)
                            or

SELECT * FROM EMP E, DEPT D

WHERE E.DEPTNO=D.DEPTNO AND DNAME ='ACCOUNTING'

Result:

Left outer join :- 
It is  used to get 'all rows from left table and only matching rows from right table'. A condition should be given to execute an left outer join.

(select * from emp  left outer join dept on emp.dno=dept.dno)
                                           or
(SELECT E.EMPNO, E.ENAME, D.DEPTNO, D.DNAME
FROM EMP E
LEFT OUTER JOIN DEPT D
ON E.DEPTNO=D.DEPTNO)

Result:

Right Outer Join :-
It is  used to get 'all rows from Right table and only matching rows from Left table'. A condition should be given to execute an right outer join

(select * from emp right outer join dept on emp.dno=dept.dno)
                                            or
(SELECT * FROM EMP E RIGHT JOIN DEPT D ON E.DEPTNO = D.DEPTNO)
                                            or
(SELECT E.EMPNO, E.ENAME, D.DEPTNO, D.DNAME
FROM EMP E
RIGHT JOIN DEPT D
ON E.DEPTNO=D.DEPTNO)

Result:

The FULL OUTER JOIN :-
This keyword return all records when there is a match in either left (table1) or right (table2) table records. A condition should be given to execute an right outer join

(select * from emp right outer join dept on emp.dno=dept.dno)
                                                or
(select * from emp2013 full outer join emp2014 on emp2013.id = emp2014.id)
                                                or
(SELECT * FROM EMP E FULL JOIN DEPT D ON E.DEPTNO = D.DEPTNO)

Result: