joins concept for oracle learners
DESCRIPTION
learning about Joins in SQL for DatabaseTRANSCRIPT
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 thenOraclejoins first two tables based on the joins condition and then compares the result with the next table and so on.
TYPES1Equi join2Non-equi join3Self join4Natural join5Cross join6Outer join Left outer Right outer Full outer7Inner join8Using clause9On clause
Assume that we have the following tables.SQL>select * from dept;
DEPTNODNAMELOC
10INVENTORYHYBD
20FINANCEBGLR
30HRMUMBAI
SQL>select * from emp;
EMPNOENAMEJOBMGRDEPTNO
111sakethanalyst44410
222sudhaclerk33320
333jaganmanager11110
444madhuengineer22240
1.EQUI JOINA 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;
EMPNOENAMEJOBDNAMELOC
111sakethanalystINVENTORYHYBD
333jaganmanagerINVENTORYHYBD
222sudhaclerkFINANCEBGLR
Using clauseSQL>select empno,ename,job ,dname,loc from emp e join dept d using(deptno);
EMPNOENAMEJOBDNAMELOC
111sakethanalystINVENTORYHYBD
333jaganmanagerINVENTORYHYBD
222sudhaclerkFINANCEBGLR
On clauseSQL>select empno,ename,job,dname,loc from emp e join dept d on(e.deptno=d.deptno);
EMPNOENAMEJOBDNAMELOC
111sakethanalystINVENTORYHYBD
333jaganmanagerINVENTORYHYBD
222sudhaclerkFINANCEBGLR
2.NON-EQUI JOINA 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;
EMPNOENAMEJOBDNAMELOC
222sudhaclerkINVENTORYHYBD
444madhuengineerINVENTORYHYBD
444madhuengineerFINANCEBGLR
444madhuengineerHRMUMBAI
3.SELF JOINJoining 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;
EMPNOENAMEJOBDEPTNO
111jagananalyst10
222madhuclerk40
333sudhamanager20
444sakethengineer10
4.NATURAL JOINNatural join compares all the common columns.Ex:SQL>select empno,ename,job,dname,loc from emp natural join dept;
EMPNOENAMEJOBDNAMELOC
111sakethanalystINVENTORYHYBD
333jaganmanagerINVENTORYHYBD
222sudhaclerkFINANCEBGLR
5.CROSS JOINThis will gives the cross product.Ex:SQL>select empno,ename,job,dname,loc from emp cross join dept;
EMPNOENAMEJOBDNAMELOC
111sakethanalystINVENTORYHYBD
222sudhaclerkINVENTORYHYBD
333jaganmanagerINVENTORYHYBD
444madhuengineerINVENTORYHYBD
111sakethanalystFINANCEBGLR
222sudhaclerkFINANCEBGLR
333jaganmanagerFINANCEBGLR
444madhuengineerFINANCEBGLR
111sakethanalystHRMUMBAI
222sudhaclerkHRMUMBAI
333jaganmanagerHRMUMBAI
444madhuengineerHRMUMBAI
6.OUTER JOINOuter join gives the non-matching records along with matching records.
LEFT OUTER JOINThis 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 don(e.deptno=d.deptno);OrSQL>select empno,ename,job,dname,loc from emp e,dept d wheree.deptno=d.deptno(+);
EMPNOENAMEJOBDNAMELOC
111sakethanalystINVENTORYHYBD
333jaganmanagerINVENTORYHYBD
222sudhaclerkFINANCEBGLR
444madhuengineer
RIGHT OUTER JOINThis 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 don(e.deptno=d.deptno);OrSQL>select empno,ename,job,dname,loc from emp e,dept d where e.deptno(+) =d.deptno;
EMPNOENAMEJOBDNAMELOC
111sakethanalystINVENTORYHYBD
333jaganmanagerINVENTORYHYBD
222sudhaclerkFINANCEBGLR
HRMUMBAI
FULL OUTER JOINThis 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 don(e.deptno=d.deptno);
EMPNOENAMEJOBDNAMELOC
333jaganmanagerINVENTORYHYBD
111sakethanalystINVENTORYHYBD
222sudhaclerkFINANCEBGLR
444madhuengineer
HRMUMBAI
7.INNER JOINThis will display all the records that have matched.Ex:SQL>select empno,ename,job,dname,loc from emp inner join dept using(deptno);
EMPNOENAMEJOBDNAMELOC
111sakethanalystINVENTORYHYBD
333jaganmanagerINVENTORYHYBD
222sudhaclerkx`FINANCEBGLR