JOINS

 

JOINS

Ø  It is used to join rows in one table to rows in another table.

Ø  Join condition we will use in where clause

Ø  If join involves more than one table oracle 1^st joins 1^st two tables based on condition and then compare the result with next table and so on.

Ø  Using table alias names in join, ex: - Select * from emp e, dept d where e.deptno=d.deptno;

Ø  Types of joins

1.EQUI JOIN

2.INNER JOIN

3.NON EQUI JOIN

4.SELF JOIN

5.CARTESIAN JOIN OR CROSS JOIN OR CARTESIAN PRODUCT

6.OUTER JOIN

7.NATURAL JOIN

1.EQUI JOIN

Ø  Based on equality condition tables are joined, only matching records are displayed, to join tables both the tables must have at least one common col with same datatype and same values but col name may be different.

Ø  Select * from emp e, dept d where e.deptno=d.deptno;

2.INNER JOIN

Ø  Inner join will also work as same as equi join.

Ø  We use on clause for inner join

Ø  Select * from emp e inner join dept d on (e.deptno=d.deptno);

Ø  select * from emp e inner join dept d on (e.deptno=d.deptno)inner join salgrade s on (e.sal between s.LOSAL and s.HISAL);

 

3.NON EQUI JOIN

Ø  Non equi join is making by relational operator other than ‘=’

Ø  It is used to join tables If values of one column of a table falls the range between column of another table

Ø  It is used between operator so it is called between join.

Ø  Select * from emp e, salgrade s where e.sal between s.LOSAL and s.HISAL;

4.SELF JOIN

Ø  It can joins the table it self

Ø  The table alias names must use in join condition

Ø  A table contain two col with same datatype and same values then self join used.

Ø  select e.empno, e.ename "EMP NAME", m.empno "MGR NO", m.ename "MGR NAME", e.deptno from emp e, emp m where e.mgr=m.empno and e.deptno=20;

5. CARTESIAN JOIN OR CROSS JOIN OR CARTESIAN PRODUCT

Ø  It results all combination of rows will be join between two tables.

Ø  This will be done by either omitting where clause or specify cross join clause.

Ø  Select * from emp, dept;

Ø  Select * from emp cross join dept cross join salgrade;

6.OUTER JOIN

Ø  It is used to retrieve all rows from one table but matching rows from another table.

Ø  It uses operator (+) to join tables.

Ø  (+) appear only in where clause.

Ø  These are three types

a)      LEFT OUTER JOIN

b)     RIGHT OUTER JOIN

c)      FULL OUTER JOIN

     a). LEFT OUTER JOIN

Ø  It results all rows from left table but matching records from right table.

Ø  Select * from emp e left join dept d on e.deptno=d.deptno;

Ø  select * from emp e, dept d where e.deptno=d.deptno(+);

     b). RIGHT OUTER JOIN

Ø  It results all rows from right table but matching records from left table.

Ø  Select * from emp e right join dept d on e.deptno=d.deptno;

Ø  select * from emp e, dept d where e.deptno(+)=d.deptno;

     c). FULL OUTER JOIN

Ø  It will fetch all rows from both tables

Ø  Select * from emp e full outer join dept d on e.deptno=d.deptno;

7.NATURAL JOIN

Ø  It will work similar like equi, inner joins. It not accept alias names.

Ø  Select * from emp natural join dept;

Ø  Natural join with USING clause

Ø  select * from emp join dept using (deptno);

Ø  select * from emp join dept using (deptno) where deptno=10;

      Please watch and subscribe my Channel

        Kamal SQL CLASSES in Telugu