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)