Joins in Oracle with examples

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.

Types:

1. Equi join
2. Non-equi join
3. Self join
4. Natural join
5. Cross join
6. Outer join

• Left Outer join
• Right Outer join
• Full Outer join

      7. Inner join

      8. On clause

      9. Using clause

Assume that, we have following tables in Oracle.

SQL> select * from dept;

SQL> select * from emp;

1. Equi Join: 

A join which contains an equal to ‘=’ operator in the joins condition.

SQL> select empno,ename,job,dname,loc from emp e,dept d where e.deptno=d.deptno;

2. Non-Equi Join:

A join which contains an operator other than equal to ‘=’ in the joins condition.

SQL> select empno,ename,job,dname,loc from emp e,dept d where e.deptno > d.deptno;

3. SELF JOIN

Joining the table itself is called self join.

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.

SQL> select empno,ename,job,dname,loc from emp natural join dept;

5. CROSS JOIN

This gives the cross products.

SQL> select empno,ename,job,dname,loc from emp cross join dept;

6. OUTER JOIN

Outer join gives the non-matching records along with matching records.

• LEFT OUTER JOIN

           This will display the all matching records and the records which are in left hand side table those that are not in right hand side table.

SQL> select empno,ename,job,dname,loc from emp e left outer join dept d on(e.deptno=d.deptno);               OR

SQL> select empno,ename,job,dname,loc from emp e,dept d where e.deptno=d.deptno(+);

• FULL OUTER JOIN

This will display the all matching records and the non-matching records from both tables.

7. INNER JOIN

This will display all the records that have matched.

SQL> select empno,ename,job,dname,loc from emp inner join dept using(deptno);

8. Using Clause

SQL> select empno,ename,job ,dname,loc from emp e join dept d using(deptno);

9. On Clause:

SQL> select empno,ename,job,dname,loc from emp e join dept d on(e.deptno=d.deptno);