JOINS :
These are
used for retrieving the data from one or more tables at a time.
Joins can be used in any of
following ways :
1
EQUI-JOINS
2
NON
EQUI-JOINS
3
SELF
JOINS
4
CROSS
JOINS
5
OUTER
JOINS
(I) EQUI-JOINS
: If two or more tables are combined using Equality condition we call it as a
Equi-join
Ex
: Que :- write a query to get the
matching records from emp and dept tables
Select e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,
e.deptno,d.deptno, d.dname,d.loc from emp e,dept d where
e.deptno=d.deptno
In the above case it will retrieve the records
from both the two tables only if the department numbers matches with the two
tables, so it will not retrieve the information of deptno- 40 from dept table
because a match is not available in emp table
(1)Inner Join
(2)Cross Join
(3)Outer Join
Inner Join : using inner join we can implement
Equi Join, non Equi join as well as self join also
(a) Equi Join in Ansi Standard :
Note : in Ansi standards to write a join
statement we explicitly used join keyword between the tables which gives
descriptiveness also to combine or join the tables with a condition we use ON keyword but not where
Loading Data from multiple tables using Equi-Join
condition
By using an equi-join we can combined the data
of any no of tables provided all these tables contains a common column
3rd
table DeptDetails structure
Note: my
previous post you can find emp and dept table structures http://sqlanddotnetpost.blogspot.in/2014/07/employee-emp-database-for-sql-server.html
Que: Write
a query to join 3 tables emp, dept and deptdetails
Using Non-Ansi Joins:
select e.ename,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,e.deptno,D.dname,d.loc,dd.did,dd.comments
from emp e,dept d,deptdetails dd where e.deptno=d.deptno and d.deptno=dd.deptno
Ansi-Standard Join :
select e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,e.deptno,d.dname,d.loc,dd.did,dd.comments
from emp e inner join dept d on e.deptno=d.deptno inner join deptdetails dd on dd.deptno= d.deptno
(b)
Non-Equi
Joins :
If we join tables with any condition other than
equality condition we call it as a non-equi join
4th
Table SalGrade
Que : Write a
query to get data from emp and salgrade table by displaying the grade of the
Employee basing on Salary
Using Non-Ansi Join
select e.empno,e.ename,e.job,e.sal,s.grade,s.losal,s.hisal from emp e,salgrade s where e.sal between s.losal and s.hisal
Using Ansi Standard Join
select e.empno,e.ename,e.job,e.sal,s.grade,s.losal,s.hisal from emp e inner join salgrade s on e.sal between s.losal and s.hisal
Que : write to get the
data using 3 tables dept,emp and sal grade
Select e.empno,e.ename,e.job,e.sal,d.deptno,d.dname,d.loc,s.grade, s.losal,s.hisal from emp e inner join dept d on e.deptno=d.deptno inner join salgrade s on e.sal between s.losal and s.hisal
(c’) Self Jon : Joining a table to itself for
getting the results is a self join
Que : write a query to get the details of employees who have
sub-ordinates under then by using join
Using Non-Ansi join
select distinct
e.ename,e.ename,e.job,e.sal,e.deptno from emp e, emp m where e.empno=m.mgr
Using
Ansi Standards
select distinct
e.empno,e.ename,e.job,e.sal,e.deptno from emp e inner join emp m on e.deptno=m.mgr
II) Cross Join
If two
or more tables are combined with each other without any condition we call it as
a Cross join. Here each row of the first table goes and joins with each row of
the second table if the 1st table has m rows and 2nd
table has n rows the output will be max row
Using Non-Ansi :
Select *
from emp e,dept d,deptdetails dd
Using Ansi Join :
Select * from emp e cross join dept d cross join
deptdetails dd
III) Outer Join
It is an extension for the equi join i.e. in an equi join
condition we will be getting the matching information from the tables used in
the query. Whereas if a record(s) of a table doesn’t have a matching data in
the other table that record(s) will not be retrieved.
In case of an outer join just like our equi join will
retrieve the matching data from all the tables as well as the unmatched data
also. Where the unmatched data can be present in the left side of table or Right
hand side table or both tables also
Outer Joins are of 3 types :
1) Left Outer Join
2) Right Outer Join
Left
outer join : Retrieves matching data from both tables as
well as unmatched data from left hand side table.
Right
outer Join : Retrieves the matching data from both the
tables as well as unmatched data from Right side Table
Full
Outer Join : Retrieves the unmatched data from both the
tables + unmatched data from left side table + unmatched data from Right side
table also.
Left
Outer Join :
Ex : select e.empno,e.ename,e.job,e.mgr,d.deptno,d.dname,d.loc from
emp e left outer join dept d on e.deptno=d.deptno
Right Outer Join :
select e.empno,e.ename,e.job,e.mgr,d.deptno,d.dname,d.loc from emp e Right outer join dept d on e.deptno=d.deptno
Full
Outer Join :
select e.empno,e.ename,e.job,e.mgr,d.deptno,d.dname,d.loc from emp e full outer join dept d on e.deptno=d.deptno
Que : Write a query to get matching and
unlatching data from the tables emp,dept and deptdetails and salgrade
Select e.empno,e.ename,e.job,d.deptno,d.dname,d.loc,dd.did,dd.comments from emp e right outer join dept d on e.deptno=d.deptno inner join deptdetails dd on d.deptno=dd.deptno
Note:
In the above query we are not retrieving the
information from salgrade table if we want to get data from that table also
with matched and unmatched data the query must be as following
Query :
Select e.empno,e.ename,e.job,s.grade,d.deptno,d.dname,s.losal,
dd.did,dd.comments
from salgrade
s inner join emp e on e.sal between s.losal and s.hisal right outer join dept d
on e.deptno=d.deptno inner join deptdetails dd on d.deptno=dd.deptno
