Joins (inner join, outer join, cross join) in SQL – SQL Server Tutorial

JOINS  :–

  • The join is a data retrieval operation performed to get data multiple tables.

              DB                                                                      DWH

tables are normalized                                      tables are de-normalized.

more joins are required                                   less joins are required.

TYPES OF JOINS :-

(i) inner join

  •                  -equi join
  •                  -non – equi join
  •                  -self join

(II) outer join

  •                   -left
  •                   -right
  •                   -full

(III) cross join

(I) inner join in sql :-

            (a) Equi join  :-

  • Joining two tables  based on common column.
  • To perform equi there should be a common column,but the name of the common column need not be same.
  • SQL server performs join operators,when more than one lable is listed in from class.

Example  :- Syntax  : from emp, dno. 

Syntax  :select<collist>from<tablist>         Where<join cond>

            [and <join cond>and<cond>—–] 

Join condition :- The two tables are joined based on this condition.

Example  :- child.fk=parent.pk(if relationship exists)

                                                          (or)

            Table1.commncol=table2.commcol(if there is no relationship)

  • Join condition is based on equal to operation
  • To join ‘n’ tables (n-1) join condition are required. 

                    Emp                                                                                dept          .

Empno        ename        sal           d.no                           d.no             dname              loc.

1                       A              5             10                                 10                A/c                –

2                       B              6              20                               20                sales              –

3                       C              4               30                              30               purchase       –

4                       D             3                10                             40               market          –

5                        E             7                  –

Display enames and dept names  :-

Select  ename,dname,     From emp, dept, Where emp.dno=dept.no 

ename            dname

A                   alcs

B                 sales

C                 purchase

D                 A/C

Note: “In case of join queries better to use table Alias”

 Select e.ename, d.dname  From emp e,dept d where e.deptno=d.dno.

Display name of the employees working for sales department.

   Select e.ename

    From emp e,dept d

    Where e.deptno=d.dno

    And  d.dname =’sales’

Display names of the employees working for sales dept& more than earn 3000.

Select e.ename

From emp e,dept d

Where e.deptno=d.dno

And d.dname=’sales’

And sal>3000

Display ename , dname, experience, netsal

Emp                 Dept             emp-expr                 emp-netsal

Empno             dno         D-no         empno          expr       empno       netsal

1                         10               10                 1                    10              1             10,000    

2                         20              20                2                    12              2             20,000 

3                         30              30                3                    15              3             30,000     

4                        10                40               4                     8                4            40,000 

Select E.ename,d.dname,x.expr

From emp e,dept d,emp_expr x

Where e.deptno=d.deptno

And x.empno=e.empno

Leave a Reply

Your email address will not be published. Required fields are marked *