20. JOINS

JOINS

·        Join  is a query which is used to retrieve data from more than one table by providing  join condition.

·        We provide the join condition in “where clause” and even in “from clause”.

·        Join condition columns must be compatible data types or same data types.

Oracle Traditional or Native joins: (prior to 9i):

o   Inner join:

§  Equi  join

§  Non-Equi  join

o   Self-join

o   Outer join

§  Left outer join

§  Right outer join

§  Full outer join

  9i joins:

·        Cross Join

·        Natural join

·        Join on

·        Join using

·        Outer join

·        Left outer join

·        Right outer join

·        Full outer join

Q: To find out the table which we have in database?

Ans: desc user_object

desc user_table

Cartesian Product:

NOTE: In the absence of join condition, if you combine more than one table than the result will be ‘Cartesian Product’ results.  Which means each record of one table will combine with multiple records of another table.

SQL> select * from emp,dept;

 ------------------------------------------------------------------------

Equi joins:  In this join we will provide the join condition with equal to (=) operator.

SQL> select * from emp, dept where emp.deptno=dept.detpno;

SQL> select * from emp e, dept d where e.detpno=d.detpno;

Note: We can provide ‘n’ no.of join conditions by separated by and (or) or.

------------------------------------------------------------------------

Non-equi join: In non-equi join we provide the join condition between the columns with other than equal to (=) operator.

SQL> select * from emp e, dept d where e.deptno!=d.deptno;

SQL> select * from emp e, dept d where e.detpno<=d.deptno;

Note: Inner join will skip null record values.

----------------------------------------------------------------------

Self-join: Join in the same table columns is called self join.

SQL> select e.empno, e.ename, m.empno, m.ename from emp e , emp m where e.mgr=m.emp no;

   Emp e                                               Emp m

Empno	Ename	Mgr
1	X	2
2	Y	3
3	Z	4
4	A
5	B	3

Empno	Ename	Mgr
1	X	2
2	Y	3
3	Z	4
4	A
5	B	3

Output:  e.empno                   e.ename             m.empno           m.ename

                   1                       x                       2                         y

                   2                       y                       3                         z

                   3                       z                       4                         a

                   5                       b                       3                         z

NOTE:  here emp table is splitting as 2 emp tables i.e. copy of emp.

  

SQL> select e.empno employno, e.ename employname, m.empno managerno, m.ename managername from emp e , emp m where e.mgr = e.empno;

NOTE : we have to split one table to ‘n’ no.of tables(virtual tables) physically the table not available but logically it takes values from virtual tables.

Virtual tabler  ---> no data (logically it takes data).

Outer join:-

Along with matched records further if we want to get additional records from either of the table we use outer joins.

We will perform outer joins with outer join operator (+).

  Left outer join:

o   In this join we will get full details from the left table and matched record of right table.

o   In this join condition (+) symbol should has to be placed at right side of equal operator.

Right outer join:

Which is reverse to that of left outer join?

ltab                                rtab

Sno

10

20

50

60

100

Sno

10

20

30

40

SQL> select rtab.sno, ltab.sno from ltab, rtab where rtab.sno(+) = ltab.sno;

Output:

rtab.sno                ltab.sno

  10                         10

  20                         20

                               50

                               60

                               100

SQL> select rtab.sno, ltab.sno from ltab, rtab where rtab.sno(+) > ltab.sno;

Output:

rtab.sno                ltab.sno

 20                        10

30                         10

40                         10

30                         20

40                         20

                             50

                             60

                             100

Left outer join example:

SQL> select rtab.sno, ltab.sno from rtab, ltab where rtab.sno = ltab.sno(+);

Rtab.sno               ltab.sno

10                         10

20                         20

30                         null

40                         null

Full outer join:

Concept of full outer join is from 9i onwards. But still if we want to

achieve full outer join result even prior to 9i.

We use following example:

In full outer join we will combine both left & right outer joins with union operator.

It display the all records from both of the rables.

SQL> select * from rtab,ltab where ltab.sno = rtab.sno(+)  union select * from rtab, ltab where ltab.sno(+) = rtab.sno;

 -------------------------------------------------------------------------------------------------

Set Operators : (vertical joins):

They are used to combine the queries so to get the compound query. They are also called vertical joins. We can join ‘n’ no.of queries with set operators. In set operators individual query results will be combine to get a final result set.

 

                                      

o   In compound query all the component queries must contain same no.of columns with compatible data types. This rule we call it as

‘Union Combination Condition’:

o   In compound queries order by clause will be allowed at the end.

o   Providing order by clause for individual component queries will not be allowed.

o   In compound queries result will be displayed with the first component query select list columns.

o   Order by clause in compound query allows only the first component query select list columns.

o   All the set operators has equal priority ,except union all , all the set operators will sort & suppress duplicate values.

----------------------------------------------------------------------------------------------

Set Operators:

1.     Union

2.     Union all

3.     Intersect

4.     Minus

5.     Multiset  (11g)

 In set operators default execution takes place from left to right but we can alter default execution by using parenthesis.

Union :

It displays the records from both tables by suppressing the duplicate records and also sort data.

Note (for restrictions) : Elimination of duplicate records becomes a problem when we use order by clause for component query.

Union all :  It displays all the records from both tables regardless of duplicating and it doesn’t sort data.

Note :  Union all is more faster than union.

Intersect :  Display the common records between tables. It also suppresses duplicate values.

Minus :  We will get records from one table which are not matching with other table. Result won’t get effect or varies. When you change the order of component query except in minus operator.

Sql> select sno from rtab union select sno from ltab;

Sql> select sno from rtab unionall select sno from ltab;

Sql> select sno from rtab intersect select sno from ltab;

Sql> select sno from rtab minus select sno from ltab;

Sql> select sno from rtab minus select sno from ltab union select * from rtab;

Sql>  select 1 from dual union select 2 from dual;

Sql> select sno from rtab union select sno from ltab order by sno;

-------------------------------------------------------------------------------------- 

Joins (9i) : (ISO/sql 1999):

1.     Cross join

2.     Natural (pure nj)

3.     Join.....using

4.     Join.....on

5.     Outer..join

1)    Left outer join

2)    Right

3)    Full

Log join (ISQ/SQL 2003)

1.     Partition outer join (log)

9i joins won’t give any guarantee for the performance.

9i syn:

          Select * from t

          [cross join t1]  |

          [natural join t1]  |(alternate)

          [[inner] join t1 {using (col [col1,....]|on con d1,....)}]  |

          [{left / right/full } [ outer ] join t1

          { using (col [col1,...]  | on (cond1,....)}]

 ---------------------------------------------------------------------------------------------

 Cross joins :  It works as same to that of Cartesian product (or) display the Cartesian result.

Sql> select * from emp cross join dept;  (56 records..Cartesian product results)

 ----------------------------------------------------------------------------------------------

Natural join :  Natural join works between table names.

It works as same to that of equi join (or) with natural join we will achieve equi join result.

·        Natural join automatically provides the equi join conditions between the columns of tables whose column names are same.

·        In natural join at least one set of columns must exist with same name.

·        In natural join column name should be same and it should be same/ compatible data type.

IMP: When a natural join becomes cross join?

Ans)  Natural join becomes cross join when column names are differing.

It’s not possible to qualify the columns which are used up by natural join.

Rtab		Ltab
Sno		Sno
10		10
20		20
30		30
40		40
		60
		100

Sql> select * from rtab;     

                         

Sql> select * from ltab;

Sql> select * from rtab,ltab where rtab.sno=ltab.sno;

Sno

10           20

Sno

10           20

                    Sno            Sno

                    10               10

                    20                20

 Sql> select * from rtab natural join ltab;    

      

Sql> select  sno from rtabnatural join ltab;    

Sql> select  rtab.sno from rtab natural join ltab;

Error:-  column used in natural join can’t have qulifier.

Sql> alter table ltab rename column sno to sn;

Ltab

Sn

10          20          30

Rtab

Sno

10          20          50          60

           Sql> select * from ltab;

           Sql> select * from rtab;

           Sql> select * from ltab natural join rtab;

(which is a Cartesian product ) i.e  4*5=20 records.

Sql> alter table rtab rename column sno to sn;

Sql> select * from ltab;      

                                

Sql> select * from rtab;

  Sql> select * from rtab natural join ltab;

Result is same to the following example.

Sql> select * from ltab,rtab where ltab.sno=rtab.sno  and ltab.loc=rtab.loc;

Disadvantage:

     In pure natural join it is not possible to specify join condition only for particular columns.  When there is more no. of same columns, same sets across the tables. 

----------------------------------------------------------------------------------------------

Join using:- In join using we use using clause to specify the columns.

So we provide equal join condition between mentioned columns.

Using clause columns can’t be qualified in an entire select statement. Using clause column must be there in both of tables.

Sql> select * from ltab join rtab using (sno);

        Result: sno     loc       loc

                    10         A         A

                    20         B         B

                    50         C         x

Sql> select  sno,ltab.loc,rtab.loc from ltab join rtab using (sno);

       

      Result:  sno       loc       loc

                    10         A         A

                    20         B         B

                    50         C         x

Sql> select sno,loc,loc from ltab join rtab using (ltab.sno);

Error: only simple column names allowed here.

Sql> select * from ltab join rtab using (sno,loc);

    

       Result: sno       loc       loc

                    10         A         A

                    20         B         B

Disadvantages: In using clause at least one set of column names must exist with same name, if not using clause is not applicable.

Note:  Join...... using clause will works like natural joins also but natural joins will not work like using clause as same join ......on clause will.

 -------------------------------------------------------------------------------------------

Join.....on(cond/.....):  (it is preferable) We use join on to provide condition.

Sql> select * from rtab join ltab on (rtab.sno1=ltab.sno);

Sno	loc

Sno1	Loc1

       

Result:                    Sno1    loc1   sno    loc    

                                   10       A      10      A

                                   20       B      20      B

                                   50       X      50     C

Sql> select * from ratb join ltab on (rtab.loc1=ltab.loc);

Sql> select * from ratb join ltab on(rtab.loc1=ltab.loc and rtab.sno1=ltab.sno);

Sql> select * from ratb join ltab on(rtab.sno1=ltab.sno and rtab.loc1=ltab.loc);

Note: Natural join , join on & join using are mutually exclusive.

Sql> select * from ratb join ltab on (rtab.sno=ltab.sno and tab.sno > rtab.sno);

                                                                             (Using normal condition)

Sql> select * from rtab joins ltab on .ltab.sno=rtab.sno and ltab.lov=rtab.loc;

                                                                           (using without parenthesis)

 NOTE: Join on will works as join ...using and additionally works on conditions.  Join on, using clause will work on alter joins also.

Outer joins:-

                 1) left outer,

                 2) right outer,

                 3) full outer.

Sql> select * from rtab left outer join ltab on (rtab.sno1=ltab.sno and          rtab.loc1=ltab.loc);

Sno1	Loc1
10         20         50         30         40	A          B          C          X          D

Sno	Loc
10           20	A           B

Sno	Loc
10          20          50          30          40	A           B           C           X           D

Sno	Loc
10          20          50          60         100           --           --           --	A         B         C         X         --         --         --         --

Sql>  select * from rtab right outer join ltab on (rtab.sno1=ltab.sno and          rtab.loc1=ltab.loc);

Sno1	Loc1
10           20	A          B

Sql>  select * from rtab full outer join ltab on (rtab.sno1=ltab.sno and          rtab.loc1=ltab.loc);

Sno1	Loc1
10          20           --           --           --          50         40         30	A           B           --           --           --           X           D           C