Joins in Oracle

JOINS IN ORACLE-different 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 
• Right outer 
• Full outer 

7     Inner join

8     Using clause
9     On clause

Assume that we have the following tables.

SQL> select * from dept;

DEPTNO	DNAME	LOC
10	INVENTORY	HYBD
20	FINANCE	BGLR
30	HR	MUMBAI

SQL> select * from emp;

EMPNO	ENAME	JOB	MGR	DEPTNO
111	saketh	analyst	444	10
222	sudha	clerk	333	20
333	jagan	manager	111	10
444	madhu	engineer	222	40

      1.      EQUI JOIN

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

Ex:

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

EMPNO	ENAME	JOB	DNAME	LOC
111	saketh	analyst	INVENTORY	HYBD
333	jagan	manager	INVENTORY	HYBD
222	sudha	clerk	FINANCE	BGLR

Using clause

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

EMPNO	ENAME	JOB	DNAME	LOC
111	saketh	analyst	INVENTORY	HYBD
333	jagan	manager	INVENTORY	HYBD
222	sudha	clerk	FINANCE	BGLR

On clause

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

EMPNO	ENAME	JOB	DNAME	LOC
111	saketh	analyst	INVENTORY	HYBD
333	jagan	manager	INVENTORY	HYBD
222	sudha	clerk	FINANCE	BGLR

      2.      NON-EQUI JOIN

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

 Ex:

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

EMPNO	ENAME	JOB	DNAME	LOC
222	sudha	clerk	INVENTORY	HYBD
444	madhu	engineer	INVENTORY	HYBD
444	madhu	engineer	FINANCE	BGLR
444	madhu	engineer	HR	MUMBAI

      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;

EMPNO	ENAME	JOB	DEPTNO
111	jagan	analyst	10
222	madhu	clerk	40
333	sudha	manager	20
444	saketh	engineer	10

      4.      NATURAL JOIN

Natural join compares all the common columns.

Ex:

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

EMPNO	ENAME	JOB	DNAME	LOC
111	saketh	analyst	INVENTORY	HYBD
333	jagan	manager	INVENTORY	HYBD
222	sudha	clerk	FINANCE	BGLR

      5.      CROSS JOIN

This will gives the cross product.

Ex:

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

EMPNO	ENAME	JOB	DNAME	LOC
111	saketh	analyst	INVENTORY	HYBD
222	sudha	clerk	INVENTORY	HYBD
333	jagan	manager	INVENTORY	HYBD
444	madhu	engineer	INVENTORY	HYBD
111	saketh	analyst	FINANCE	BGLR
222	sudha	clerk	FINANCE	BGLR
333	jagan	manager	FINANCE	BGLR
444	madhu	engineer	FINANCE	BGLR
111	saketh	analyst	HR	MUMBAI
222	sudha	clerk	HR	MUMBAI
333	jagan	manager	HR	MUMBAI
444	madhu	engineer	HR	MUMBAI

      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.

Ex:

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(+);

EMPNO	ENAME	JOB	DNAME	LOC
111	saketh	analyst	INVENTORY	HYBD
333	jagan	manager	INVENTORY	HYBD
222	sudha	clerk	FINANCE	BGLR
444	madhu	engineer

RIGHT OUTER JOIN

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

Ex:

SQL> select empno,ename,job,dname,loc from emp e right 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;

EMPNO	ENAME	JOB	DNAME	LOC
111	saketh	analyst	INVENTORY	HYBD
333	jagan	manager	INVENTORY	HYBD
222	sudha	clerk	FINANCE	BGLR
			HR	MUMBAI

FULL OUTER JOIN

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

Ex:

SQL> select empno,ename,job,dname,loc from emp e full outer join dept d

on(e.deptno=d.deptno);

EMPNO	ENAME	JOB	DNAME	LOC
333	jagan	manager	INVENTORY	HYBD
111	saketh	analyst	INVENTORY	HYBD
222	sudha	clerk	FINANCE	BGLR
444	madhu	engineer
			HR	MUMBAI

      7.      INNER JOIN

This will display all the records that have matched.

Ex:

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

EMPNO	ENAME	JOB	DNAME	LOC
111	saketh	analyst	INVENTORY	HYBD
333	jagan	manager	INVENTORY	HYBD
222	sudha	clerkx`	FINANCE	BGLR

If you liked the above post Please go through below useful links too,

• What is Index and Its Use?
• How to do Top-N analysis in Oracle
• Correlated Sub Queries
• What is an Inline View?