JOINS

JOINS

The purpose of a join is to combine the data across tables.

  • A join is actually performed by the where clause which combines the specified rows of tables.
  • 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

  • Equi join
  • Non-equi join
  • Self join
  • Natural join
  • Cross join
  • Outer join 

 

  1. Left outer
  2. Right outer 
  3. Full outer

 

  • Inner join
  • Using clause
  • On clause

Assume that we have the following tables.
SQL> select * from dept;
DEPTNO DNAME LOC
—— ———- ———-
10 mkt hyd
20 fin bang
30 hr bombay

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

EQUI JOIN
A join which contains an ‘=‘ 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 mkt hyd
333 jagan manager mkt hyd
222 sudha clerk fin bang

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 mkt hyd
333 jagan manager mkt hyd
222 sudha clerk fin bang

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 mkt hyd
333 jagan manager mkt hyd
222 sudha clerk fin bang

NON-EQUI JOIN
A join which contains an operator other than ‘=‘ 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 mkt hyd
444 madhu engineer mkt hyd
444 madhu engineer fin bang
444 madhu engineer hr bombay

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

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 mkt hyd
333 jagan manager mkt hyd
222 sudha clerk fin bang

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 mkt hyd
222 sudha clerk mkt hyd
333 jagan manager mkt hyd
444 madhu engineer mkt hyd
111 saketh analyst fin bang
222 sudha clerk fin bang
333 jagan manager fin bang
444 madhu engineer fin bang
111 saketh analyst hr bombay
222 sudha clerk hr bombay
333 jagan manager hr bombay
444 madhu engineer hr bombay

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 mkt hyd
333 jagan manager mkt hyd
222 sudha clerk fin bang
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 mkt hyd
333 jagan manager mkt hyd
222 sudha clerk fin bang
hr bombay

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 mkt hyd
111 saketh analyst mkt hyd
222 sudha clerk fin bang
444 madhu engineer
hr bombay

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 mkt hyd
333 jagan manager mkt hyd
222 sudha clerk fin bang

Related posts:

Leave a Reply