SQL SERVER -- JOINS -- Part-12

JOINS:-

                          Joins are used to retrieve data from two or more tables or views based on some common columns.

Types Joins:-

•   Inner Join
•   Left Outer Join
•   Right Outer Join
•   Full Outer Join
•   Cross Join
•   Self Join

            -- How to Create New DataBase

CREATE DATABASE SQLSERVER

GO

-- How to go inside to the Database

USE SQLSERVER

GO

-- How to Create New Table

CREATE TABLE EMP

(

       EMPID INT

      ,EMPNAME VARCHAR(50)

      ,SAL MONEY

      ,DOJ DATE

      ,LOC VARCHAR(50)

      ,GENDER CHAR(6)

      ,MagrID INT

      ,DEPTNO INT

)

-- INSERT Sample Data Into the Table.

INSERT INTO EMP(EMPID, EMPNAME, SAL, DOJ, LOC, GENDER, MagrID, DEPTNO)

VALUES(1,'Santhosh',10000,'01-12-2014','Bangalore','M',3,101)

INSERT INTO EMP

VALUES(2,'Kumari',8000,'11-05-2013','Chennai','F',3,104)

INSERT INTO EMP(EMPID, EMPNAME, SAL, DOJ, LOC, GENDER, MagrID, DEPTNO)

VALUES(3,'Kamesh',75000,'01-12-2012','Mumbai','M',NULL,105)

INSERT INTO EMP(EMPID, EMPNAME, SAL, LOC, GENDER, MagrID, DEPTNO)

VALUES(4,'Arun',44000,'Hyderabad','m',5,103)

INSERT INTO EMP(EMPID, EMPNAME, SAL, DOJ, LOC, GENDER, MagrID, DEPTNO)

VALUES(5,'Aishwarya', 15000, '01-12-2014','Bangalore','f',3,103)

-- Creaeting Dept Table

CREATE TABLE DEPT

(

  DEPTID INT PRIMARY KEY

 ,DEPTNAME VARCHAR(50)

)

-- INSERT SOME SAMPLE DATA INTO Dept Table.

INSERT INTO DEPT(DEPTID,DEPTNAME) VALUES(101,'HR')

INSERT INTO DEPT(DEPTID,DEPTNAME) VALUES(102,'TESTING')

INSERT INTO DEPT(DEPTID,DEPTNAME) VALUES(103,'DEVOLOPMENT')

INSERT INTO DEPT(DEPTID,DEPTNAME) VALUES(104,'ADMIN')

INSERT INTO DEPT(DEPTID,DEPTNAME) VALUES(105,'FINANCE')

-- Verify the data from the EMP and DEPT Tables

SELECT * FROM EMP

SELECT * FROM DEPT

INNER JOIN :- 

                         Inner Join is used to retrieve matching records from both the tables based on common column.

--INNER JOIN

     SELECT E.EMPNAME

              ,D.DEPTNAME

        FROM EMP AS E INNER JOIN DEPT AS D

        ON E.DEPTNO=D.DEPTID

LEFT OUTER JOIN :-

                                 Left Outer Join is used to retrieve all the records from Left Side table and matching records from right side table based on common column.

--LEFT OUTER JOIN

     SELECT E.EMPNAME

              ,D.DEPTNAME

        FROM EMP AS E LEFT OUTER JOIN DEPT AS D

        ON E.DEPTNO=D.DEPTID

RIGHT OUTER JOIN :-

                                              Right Outer Join is used to retrieve all the records from Right Side table and matching records from Left side table based on common column.

--RIGHT OUTER JOIN

     SELECT E.EMPNAME

              ,D.DEPTNAME

        FROM EMP AS E RIGHT OUTER JOIN DEPT AS D

        ON E.DEPTNO=D.DEPTID

FULL OUTER JOIN :-

                                         Full Outer Join = Left Outer Join + Right Outer Join

--FULL OUTER JOIN

     SELECT E.EMPNAME

              ,D.DEPTNAME

        FROM EMP AS E FULL OUTER JOIN DEPT AS D

        ON E.DEPTNO=D.DEPTID

SELF JOIN :-

                           Self Join is nothing but joining the same table more than once to get required information.

--SELF JOIN

     SELECT E1.EMPNAME AS 'ManagerName'

              ,E2.EMPNAME AS 'EmployeeName'

        FROM EMP AS E1 LEFT JOIN EMP AS E2

        ON E1.EMPID=E2.MAGRID

CROSS JOIN :-

                               If we are receiving data from more than one table with out any join condition then called as Cross Join OR Cartesian Product.
   If a Table contains M record and another Table contains N records, then Cartesian Product will retrieve M*N records.

--CROSS JOIN

     SELECT E.EMPNAME

              ,D.DEPTNAME

     FROM EMP AS E,DEPT AS D