SQL Server : Joins

                                                                      JOINS

                                                                                                               

                Joins are used to retrieve data from multiple tables to full fill the business needs.If you want to join 'n' tables we should use n-1 joining conditions. To join tables we need common columns or related columns

Types

1. Inner Join

                * EQUI Join

                * NON Equi

                * Self Join

2. Outer Join

                * Left Outer Join

                * Right Outer Join

                * Full Outer Join

3. Cross Join

Equi Join:

Based on the  equality condition and common columns we are retrieving data from multiple tables.

Joining conditional columns must belongs to same data type

WAQ to display the employees who are working in chicago loaction?

SELECT ENAME
 ,SAL
 ,E.DEPTNO
 ,DNAME
 ,LOC
FROM EMP E
 ,DEPT D
WHERE E.DEPTNO = D.DEPTNO
 AND LOC = 'CHICAGO'

WAQ to display dname,sum(sal) from emp,dept tables?

SELECT DNAME
 ,SUM(SAL)
FROM EMP E

 ,DEPT D
WHERE E.DEPTNO = D.DEPTNO
GROUP BY DNAME

WAQ to display depatment wise summarized salary where salary more than 10000?

SELECT DNAME
 ,SUM(SAL)
FROM EMP E
 ,DEPT D
WHERE E.DEPTNO = D.DEPTNO
GROUP BY DNAME
HAVING SUM(SAL) > 10000

NonEqui Join:

Here we can retrieve data from multiple tables by using other than equality condition.

This join is also called BETWEEN AND  join

Ex:

SELECT ENAME
 ,SAL
 ,LOSAL
 ,HISAL
FROM EMP E
 ,SALGRADE S
WHERE SAL BETWEEN LOSAL
  AND HISAL

Self-Join :

                Joining a table itself is called as self join. Whenever we are using self join, joining conditional columns must belongs to same data type and also internally should have some relationship

WAQ: Display employee names and their mangers names

SELECT E1.ENAME 'EMPLOYEE NAME'
 ,E2.ENAME "MANAGER"
FROM EMP E1
 ,EMP E2
WHERE E1.MGR = E2.EMPNO

WAQ to display who are joining before their managers or senior most employees than mangers?

SELECT E1.ENAME 'EMPLOYEE NAME'
 ,E2.ENAME "MANAGER"
FROM EMP E1
 ,EMP E2
WHERE E1.MGR = E2.EMPNO
 AND E1.HIREDATE < E2.HIREDATE

OUTER JOINS:

                In equijoin operation there may be a possibility to loss some information. To recover the loss information from equi join output we use outer joins. Outer joins are again classified into

Leftouterjoin

It displays all the records from first (left-hand side) table and mathching records from rightside table.

SELECT ENAME
 ,SAL
 ,EMP.DEPTNO
 ,DNAME
 ,LOC
FROM DEPT
LEFT JOIN EMP ON EMP.DEPTNO = DEPT.DEPTNO

Rightouter Join:

It displays all the records from the second(right hand side) table and matching records from left hand side table.

SELECT ENAME
 ,SAL
 ,EMP.DEPTNO
 ,DNAME
 ,LOC
FROM DEPT
LEFT JOIN EMP ON EMP.DEPTNO = DEPT.DEPTNO

Full Outer Joins

It displays all the records from both first and second tables.

SELECT ENAME
 ,SAL
 ,EMP.DEPTNO
 ,DNAME
 ,LOC
FROM DEPT
FULL JOIN EMP ON EMP.DEPTNO = DEPT.DEPTNO

Cross Join:

                It is the product of different tables it means that  'n' nof records in one table 'm' nof records from another table so it gives product of m*n

Ex:

SELECT *
FROM EMP
CROSS JOIN DEPT