JOIN in ORACLE

The JOIN operations are the very confusing queries between two tables. However some join results can be achieved by 
using an explicit equality test also in a WHERE clause, such as "WHERE t1.col1 = t2.col2".

1. The purpose of a join is to combine the data across tables.
2. A join is actually performed by the where clause which combines the specified rows of tables.
3. If a join involves in more than two tables then Oracle joins first two tables based on the joins condition and then compares the 
   result with the next table and so on.
   
Type of JOIN Operations:
1     Equi-join  or  Using clause  or  On clause
2     Non-Equi join
3     Self join
4     Natural join
5     Cross join
6     Inner join
7     Outer join 
            Left outer 
            Right outer 
            Full outer 

---------DB Tables and Data Populations--------------        
    
CREATE TABLE DEPT (
DEPTNO NUMBER(3) NOT NULL,
DNAME VARCHAR2(50) NOT NULL,
LOC VARCHAR2(50) NULL,
CONSTRAINT DEPT_PK PRIMARY KEY (DEPTNO) 
);

CREATE TABLE EMP(
EMPNO NUMBER(3) NOT NULL,
ENAME VARCHAR2(50) NOT NULL,
JOB VARCHAR2(30) NOT NULL,
MGR NUMBER(3) NOT NULL,
DEPTNO NUMBER(3),
CONSTRAINT EMP_PK PRIMARY KEY (EMPNO),  
CONSTRAINT EMP_FK FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO)
);

INSERT INTO DEPT VALUES('10','INVENTORY','HYBD');
INSERT INTO DEPT VALUES('20','FINANACE','BGLR');
INSERT INTO DEPT VALUES('30','HR','MUMBAI');
INSERT INTO DEPT VALUES('40','IT','DELHI');
INSERT INTO DEPT VALUES('50','ENGINEERING','BGLR');
INSERT INTO DEPT VALUES('60','PROD_SUPPORT','BGLR');

INSERT INTO EMP VALUES('111','Deepak','Engineer','114','50');
INSERT INTO EMP VALUES('112','Rajesh','Manager','113','20');
INSERT INTO EMP VALUES('113','Chandan','Systems','112','40');
INSERT INTO EMP VALUES('114','Devansh','Analyst','111','10');
INSERT INTO EMP VALUES('115','Saransh','HR_Team','115','30');
INSERT INTO EMP VALUES('116','Mummy','HR_Team','115','30');        
    
---------DB Tables and Data Populations--------------
            
1. EQUI JOIN, Using Clause and ON Clause gives same return.
     EQUI Join: A Join which contains an equal to ‘=’ operator in the joins condition.
     Using clause: Use of Using Clause.
     On Clause: Use of On Clause.
        
        Ex: SELECT EMPNO,ENAME,JOB,DNAME,LOC,E.DEPTNO FROM EMP E, DEPT D WHERE E.DEPTNO=D.DEPTNO;    //DEPTNO=60 Missing from Result.        
        Ex: SELECT EMPNO,ENAME,JOB,DNAME,LOC,DEPTNO FROM EMP E JOIN DEPT D USING (DEPTNO);           //DEPTNO=60 Missing from Result.    
        Ex: SELECT EMPNO,ENAME,JOB,DNAME,LOC,E.DEPTNO FROM EMP E JOIN DEPT D ON(E.DEPTNO=D.DEPTNO);  //DEPTNO=60 Missing from Result.            

2. NON-EQUI JOIN: A join which contains an operator other than equal to ‘=’ in the joins condition, means < or >.
   However you should be careful while using such queries on common column.
   Ex: SQL> SELECT EMPNO,ENAME,JOB,DNAME,LOC,E.DEPTNO FROM EMP E,DEPT D WHERE E.DEPTNO > D.DEPTNO;

3. SELF JOIN: Joining the table itself is called self join.
   Ex: SQL> SELECT E1.EMPNO,E2.ENAME,E1.JOB,E2.DEPTNO FROM EMP E1,EMP E2 WHERE E1.EMPNO=E2.MGR;

4. NATURAL JOIN: Natural join compares all the common columns. If a column is matched (DEPTNO here), data will display in ASC Order of DEPTNO.
   Ex: SQL> SELECT EMPNO,ENAME,JOB,DNAME,LOC,DEPTNO FROM EMP NATURAL JOIN DEPT; //Same AS EQUI JOIN

5. CROSS JOIN: This will gives the cartesial product.
   Ex: SQL> SELECT EMPNO,ENAME,JOB,DNAME,LOC FROM EMP CROSS JOIN DEPT;   //Total m*n records. m FROM EMP, n FROM DEPT table.

6. INNER JOIN: This will display all the records that have matched. Same as EQUI Join.
   Ex: SQL> SELECT EMPNO,ENAME,JOB,DNAME,LOC FROM EMP INNER JOIN DEPT USING (DEPTNO);
   
7. OUTER JOIN: Outer join gives the non-matching records along with matching records.
   LEFT OUTER JOIN: A join between two tables with an explicit join clause, Showing unmatched rows from the first table (as null) along with All matched Rows.
   RIGHT OUTER JOIN: A join between two tables with an explicit join clause, Showing unmatched rows from the second table (as null) along with All matched Rows.
   FULL OUTER JOIN: A join between two tables with an explicit join clause, Showing unmatched rows from both tables (as null) along with All matched Rows.
   
   LEFT OUTER JOIN:
    SELECT EMPNO,ENAME,JOB,DNAME,LOC,E.DEPTNO,D.DEPTNO FROM EMP E LEFT OUTER JOIN DEPT D ON(E.DEPTNO=D.DEPTNO); //Unmatched DEPT rows will show as null.
    SELECT EMPNO,ENAME,JOB,DNAME,LOC,E.DEPTNO,D.DEPTNO FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO(+);  //Unmatched DEPT rows will show as null.
    
   RIGHT OUTER JOIN:
    SELECT EMPNO,ENAME,JOB,DNAME,LOC,E.DEPTNO,D.DEPTNO FROM EMP E RIGHT OUTER JOIN DEPT D ON(E.DEPTNO=D.DEPTNO);  //Unmatched EMP rows will show as null.
    SELECT EMPNO,ENAME,JOB,DNAME,LOC,E.DEPTNO,D.DEPTNO FROM EMP E,DEPT D WHERE E.DEPTNO(+)=D.DEPTNO;   //Unmatched EMP rows will show as null.
   
   FULL OUTER JOIN: SELECT EMPNO,ENAME,JOB,DNAME,LOC,E.DEPTNO,D.DEPTNO FROM EMP E FULL OUTER JOIN DEPT D ON(E.DEPTNO=D.DEPTNO);


For more details, please refer: http://www.javatpoint.com/oracle-create-table
--------------------------------END------------------------------------