JOIN in SQL

 “It is a concept or a mech. Implemented on select statement to extract data from multiple tables.” To extract date from multiple tables the table must have a relation and depending on the relation join is categories in 4 diff types.
1.            Equijoin
2.            Non equijoin
3.            Self or inner join
4.            Outer join
EQUI JOIN:
        It is a type of join which will extract date from multiple tables won is available.
Emp                                                                                    
     Emp no
     Name
       Sal
      Dept no
        Xid
         1
        X
        2300
        20
        X1
        2
        Y
        2500
        10
        X2
        3
        Z
        3600
        20
        X3
        4
        Aa      
        9300
        30
        X4
        5
        Bb
        6300
        10
        X5

Dept


      Dept no
       Dname
         Loc
        10
        Sales
       CTC
         20
        purchase
      BBSR
         30
        Admin
       RKL
        40
        Market
       BPD









    Above graphic shows both the tables have a common attributes called dept no. to extract data from above table’s user can equijoin.

Syntax:
Select tab1 .col,tab1.col,tab2.col,tab2.col…………………..n
From tab1,tabl2
Where tab1.col=tab2.col;

Q. Write query to display emp no, name, sal,dept  name ,loc of each employee.
Ans – select emp.name,emp.emp no, emp.sal,dept.dname,dept.loc
From emp, dept
Where emp.dept no = dept.dept no;

NOTE:
        Table name prefix with the column name is optional but it becomes mandatory whenever a common attribute is phased.


Select emp no, ename, sal, dept no,loc
From emp, dept
Where emp.deptno=dept.dept no;
        Whenever joins statement is executed each record of the transaction table will be checked max that many no. of times the no. of records available in the master table.                                    

X
        Xid
       xname
         X1
            Xa
         X2
            Xb
         X3
            Xc
         X4
            Xd
        X5
            xe










Select emp no,name, sal, dname, loc , xname
From emp, dept,x
Where emp.dept no= dept.dept no
And dept.xid=x.xid;

Outer join:
        It is a type of join which will extract date from multiple tables’ whether a condition is satisfied or not. In other words it is responsible to extract all data from multiple tables.
        Write query which will display empno,name,sal,dname,loc of these employee who are working in a specific dapt along with that it must display those dept. information in which no persons are working
Select empno,name,sal,dname,loc
From emp,dept
Were emp.deptno(+)=dept.deptno;
        In outer join (+) is write to that side of the condition where the values is deficient. Significance of (+) is to add a null record in deficient site table.

Non Equijoin:
        It is a type of join which will extract date from multiple tables whenever no common attribute is available but there must be a relationship.

        Grade
        Lowsal
        Hisal
        A
        700
        1200
        B
        1201
        3000
        C
        3001
        5000        
        D
        5001
        8000
        E
        8001
        9999
Emp                                                                                                                                                                        salgrade
        Emp no
        Name
        Sal
        Dept no
        1
        X
        2300
        20
        2
        Y
        2500
        10
        3
        Z
        3600
        20
        4
        Aa
        9300
        30
        5
        Bb
        6300
        10

        Above example shows no common attribute is available in between the tables but there is a relationship between sal column of emp table and lowsal,hisal of salgrade table.


Write query which will display emp no, name,sal,grade of each employee
Select empno,name,sal,grade
From emp,salgrade
Where sal between lowsal and hisal;

Working with table alias:
        Table alias is a concept which is responsible to provide duplicate name to an existing table. It is only meant for joint statement and it is purely temporary in nature.
Select e.empno,e.name,e.sal,d.dname,d.loc
From emp / e, dept / d
Where e.deptno=d.deptno;
        Above example shows e and d are two table aliases respectively given for emp and dept and e and d are temp in nature.
SELF JOIN:
        It is a type of join where a table is joined with itself to get output. In self join same table is treated as master as well as child. It is otherwise known as inner join.
Xemp
The side e.x. shows emp table
Having relation with itself using
mgr and expno column. To extract the daa from                                                           
emp by joining with itself user must use table alias     
                                       
        Empno
        Name
        Mgr
        deptno
        1
        X
        2
        20
        2
        Y
        3
        10
        3
        Z
        
        20
        4
        Aa
        3
        30
        5
        Bb
        4
        10









To get the following output
X is working under y.
Y is working under z etc.
Ans                                                                                                            
Select w.name ||’ is working under ‘||m.name
From emp / w, emp /m
Where w.mgr=m.empno;
Select w.name ||’ is working under ‘||m.name
From emp / w, emp /m
Where w.mgr=m.empno(+);

Above example is an example of left outer join which is responsible to display that employee who is not working under anybody (i.e. manager)