Ø 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
Ø Left outer
Ø Right outer
Ø 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
No comments:
Post a Comment