OUTER JOIN   :-

Equi joins returns only matching records but not un-matching records.

To get the un-matching record also,we need to perform,”out-join”.

Outer joins are three types

  • Left outer join
  • Right outer join
  • Full outer join

(I) Left outer join:-   

Left outer joins returns all records from left side table and matching records from right side table.

Display enames & deptnames

Select e.ename,d.dname From emp e,dept  d Where e.deptno=*.d.deptno

Error because SQL server 2005 doesn’t allow the Non-ANSI  std in outerjoin

ANSI STYLE of left outer join

Select e.ename,d.dname From emp e left outer  join dept d Or e.deptno= d.deptno

          Emp                                                            Dept

Eno       Name       dno                             Dno            dname

1                 A             10                                10               Alcs

2                 B              20                               20               Research       

3                 C              30                               30                 Sales

4                 D              10                               40                operation    

5                 E               —

O/P  :—        ename                        dname

                        A                                   ALCS

                        B                                    Research -> Left Outer

                        C                                     Sales

                        D                                     ALCS

                        E                                     —-

(II) Right outer join  :–

It returns all records from right side table and matching records from left side.

Display enames & dnames

Select e.ename,d.dname  From emp e  right outr join dept d Or  e.deptno=d.deptno.

O/P :–             ename                 dname

                                 A                      ALCS

                                 B                      Research

                                 C                      Sales -> Right outer

                                 D                     ALCS

                                 O                     Operations 

(III) FULL OUTER JOIN  :-

  • Returns all records from both table. 

O/P  :–

              A                   ALCS

              B                   Research

              C                   Sales

              D                   ALCS

              E                    ——

              —                  Operation

Select e.ename,d.dname From emp e full outer join dept d On e.deptno=d.deptno.

Non-Ansi std of full outer join {i:e  2000 SQL server}

Select e.ename, d.dname From emp e,dept d Where e.deptno= *d.deptno Union Select e.ename, d.dname From emp e, dept d Where e.deptno *= d.deptno

CROSS JOIN  :-

  • It returns cross product of two tables.
  • SQL server performs cross join when we submit the select statement with  alt join condition.

Example  :–            Orders               Discount

                                     Ordamt                     dis

                                    1,00,000                    5%

                                                                        20%

                                                                        40%

select ordamt, dis (ordamt- (ordamt*dis/100))

        Netamt          from         orders       ,discount.

O/P:–   Ordamt             dis                netamt

               1,00,000           7                   93,000

               1,00,000            10                90,000

                1,00,000            12                88,000

                1,00,000             13               87,000