JOINS IN SQL: Joins are used for retrieving the data from one or more tables at a time. Joins can be classified into the following types.
EQUI JOIN
INNER JOIN
OUTER JOIN
LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN
NON EQUI JOIN
SELF JOIN
CROSS JOIN
NATURAL JOIN
EQUI JOIN: If two or more tables are combined using equality condition then we call as a Equi join.
Ex: WAQ to get the matching records from EMP and DEPT tables
Sol: SELECT * FROM EMP, DEPT WHERE (EMP.EID=DEPT.DNO) (NON- ANSI STANDARD)
Sol: SELECT E.EID, E.ENAME, E.SALARY, D.DNO, D.DNAME FROM EMP E, DEPT D WHERE E.EID=D.DNO (ANSI STANDARD)
INNER JOIN: Inner join return only those records that match in both table
Ex: SELECT * FROM EMP E INNER JOIN DEPT D ON E.EID=D.DNO
OUTTER JOIN: It is an extension for the equi join. In equi join condition we will be getting the matching data from the tables only. So we loss un matching data from the tables.
To overcome the above problem we use outer join which are used to getting matching data as well as UN matching data from the tables. This outer join again classified into three types
LEFT OUTER JOIN: It will retrieve or get matching data from both table as well as un matching data from left hand side table
Ex: SELECT * FROM EMP LEFT OUTER JOIN DEPT ON EMP.EID=DEPT.DNO;
RIGHT OUTER JOIN: It will retrieve or get matching data from both table as well as un matching data from right hand side table
Ex: SELECT * FROM EMP RIGHT OUTER JOIN DEPT ON EMP.EID=DEPT.DNO;
FULL OUTER JOIN: It will retrieve or get matching data from both table as well as un matching data from left hand side table plus right hand side table also.
Ex: SELECT * FROM EMP FULL OUTER JOIN DEPT ON EMP.EID=DEPT.DNO;
NON EQUI JOIN: If we join tables with any condition other than equality condition then we call as a non equi join.
Ex: SELECT * FROM EMP, SALGRADE WHERE (SALARY > LOWSAL) AND (SALARY < HIGHSAL)
SELF JOIN: Joining a table by itself is known as self join. Whenever we having some relations between the columns within the table then we use self join.
Ex: SELECT E.EID, E.ENAME MANAGERS, M.SALARY FROM EMP E, EMP M WHERE E.EID=M.EID.
CROSS JOIN: Cross join is used to join more than two tables without any condition we call as a cross join. In cross join each row of the first table join with each row of the second table.
So, if the first table contain ‘m’ rows and second table contain ‘n’ rows then output
will be ‘m*n’ rows.
Ex: SELECT * FROM EMP, DEPT
Ex: SELECT * FROM EMP CROSS JOIN DEPT
NATURAL JOIN: It is not support for SQL SERVER but supports ORACLE
Joins with Three Tables:
select * from Employee
select * from Dept
select * from student
--------------------------------------
select * from Employee,Dept,student where
(Employee.Deptno=Dept.Deptno) and
(Dept.Deptno=student.sid)(Equijoin example)
-------------------------------------- selecte.EmpID,e.EmpName,e.Deptno,d.Deptno,d.Dname,d.Location,s.si
d,s.sname,s.fee from Employee e inner join Dept d on (e.Deptno=d.Deptno)join student s on(e.Deptno=s.sid)(Innerjoin example)
--------------------------------------
selecte.EmpID,e.EmpName,e.Deptno,d.Deptno,d.Dname,d.Location,s.si d,s.sname,s.fee from Employee e left outer join Dept d on (e.Deptno=d.Deptno)join student s on(e.Deptno=s.sid)(Leftouterjoin example)
-------------------------------------- selecte.EmpID,e.EmpName,e.Deptno,d.Deptno,d.Dname,d.Location,s.si
d,s.sname,s.fee from Employee e right join Dept d on (e.Deptno=d.Deptno)join student s on(d.Deptno=s.sid)((Rightouterjoin example)
------------------------------------- selecte.EmpID,e.EmpName,e.Deptno,d.Deptno,d.Dname,d.Location,s.si
d,s.sname,s.fee from Employee e full outer join Dept d on (e.Deptno=d.Deptno)join student s on(e.Deptno=s.sid)((Fullouterjoin example)
0 comments:
Post a Comment