Asp.Net and SQL SERVER: Joins in SQL SERVER

DECLARE @Employee TABLE
(
 EmpID  INT,
 EmpName  VARCHAR(50),
 DeptID  INT
)

INSERT INTO @Employee
SELECT 1,'kannu',1 UNION ALL
SELECT 2,'Ram',1 UNION ALL
SELECT 3,'Kumar',2 UNION ALL
SELECT 4,'Murali',3 UNION ALL
SELECT 5,'Ranjith',4 UNION ALL
SELECT 6,'Suresh',4 UNION ALL
SELECT 7,'Selva',5 UNION ALL
SELECT 8,'Muthu',6 

DECLARE @Department TABLE
(
 DeptID  INT,
 DeptName VARCHAR(50)
)

INSERT INTO @Department
SELECT 1,'Account' UNION ALL
SELECT 2,'Testing' UNION ALL
SELECT 3,'Web Develop' UNION ALL
SELECT 5,'PDA Develop' UNION ALL
SELECT 6,'Java' UNION ALL
SELECT 7,'DBA'

What is Join in SQL SERVER?

The JOIN keyword is used in an SQL statement to query data from two or more tables, based on a relationship between certain columns in these tables.
Join Types

Inner Join
- Inner Join
- Equi-Join
- Natural Join
Outer join
- Left Join
  - Left Join Excluding Inner Join
- Right Join
  - Right Join Excluding Inner Join
- Full Outer Join
  - Outer Join Excluding Inner Join Or Left & Right Joins Excluding Inner Join
Cross Join / Cartesian product
Self Join / Auto Join

Join
The query compares each row of table(@Employee) with each row of table(@Department) to find all pairs of rows which satisfy the join predicate.

Inner Join
The query produces a new result set by combining the column values of both the table @Employee and @Department.
It will produce the resultset if there is atleast one match.

An inner join is a join with a join condition that may contain both equality and non-equality sign whereas an equijoin is a join with a join condition that only contain only equality sign. So we can say an equijoin is a type of inner join containing (Equal)= operator in the join condition.

It is good to know the difference between join and INNER JOIN keyword. Actually there is no difference. If we write JOIN then by default INNER JOIN is performed.

Equi-join or Inner Join
Join based on equality test. Using other operator disqualifies equijoin.

SELECT A.EmpID  ,
       A.EmpName,
       A.DeptID  ,
       B.DeptID ,
       B.DeptName
FROM   @Employee A
       INNER JOIN @Department B
 ON     A.DeptID=B.DeptID

Complex Join Or Inner Join
If join nested then it is called complex join
Jon based on more then one column from each table.

Natural Join
Select the Particular column in Both TableA and TableB. Both table should have same no of columns and names (i.e. Identical columns in both tables). This is accomplished by using union operator in both the select statement.

SELECT EmpID  ,
       EmpName,
       DeptID
FROM   @Employee
UNION --Eliminates duplicates
--UNION ALL --Get all the records from both the table
SELECT EmpID  ,
       EmpName,
       DeptID
FROM   @Employee

Outer Join
Left Outer Join
Display all columns in left side and only Match values in right side. Remaining values will be NULL.

SELECT A.EmpID  ,
       A.EmpName,
       A.DeptID  ,
       B.DeptID ,
       B.DeptName
FROM   @Employee A
       LEFT OUTER JOIN @Department B
       ON     A.DeptID=B.DeptID

Left Join Excluding Inner Join

SELECT A.EmpID  ,
       A.EmpName,
       A.DeptID  ,
       B.DeptID ,
       B.DeptName
FROM   @Employee A
       LEFT JOIN @Department B
       ON     A.DeptID=B.DeptID
WHERE B.DeptID IS NULL

Difference between On clause and Where clause when used with left join

SELECT E.EmpID  ,
       E.EmpName,
       D.DeptName
FROM   @Employee             AS E
       LEFT JOIN @Department AS D
       ON     D.DeptID = E.DeptID
       AND
              (
                     D.DeptName = 'Account'
              OR     D.DeptName = 'Java'
              )
              
SELECT E.EmpID  ,
       E.EmpName,
       D.DeptName
FROM   @Employee             AS E
       LEFT JOIN @Department AS D
       ON     D.DeptID = E.DeptID
WHERE (
              D.DeptName = 'Account'
       OR     D.DeptName = 'Java'
       )

Now let us understand ON clause it is apply before JOIN that is why it retrieves all the result of Table2 where there are Flag = 1 but it does not affect Table1 so it retrieves all the rows of table1. When WHERE clause is applied it applies to complete result so it removes all the rows from Table1 and Table2 where Flag is not equal to 1, essentially keeping flag = 1 rows from Table1 and Table2.
Right Outer Join
Just Opposite Of Left Outer Join

SELECT A.EmpID  ,
       A.EmpName,
       A.DeptID  ,
       B.DeptID ,
       B.DeptName
FROM   @Employee A
       RIGHT JOIN @Department B
       ON     A.DeptID=B.DeptID

Right Join Excluding Inner Join

SELECT A.EmpID  ,
       A.EmpName,
       A.DeptID  ,
       B.DeptID ,
       B.DeptName
FROM   @Employee A
       RIGHT JOIN @Department B
       ON     A.DeptID=B.DeptID
WHERE A.DeptID IS NULL

Full Outer Join
A full outer join combines the results of both left and right outer joins. The joined table will contain all records from both tables, and fill in NULLs for missing matches on either side.

SELECT A.EmpID  ,
       A.EmpName,
       A.DeptID  ,
       B.DeptID ,
       B.DeptName
FROM   @Employee A
       FULL OUTER JOIN @Department B
       ON     A.DeptID=B.DeptID

Outer Join Excluding Inner Join Or Left & Right Joins Excluding Inner Join

SELECT A.EmpID  ,
       A.EmpName,
       A.DeptID ,
       B.DeptID ,
       B.DeptName
FROM   @Employee A
       FULL OUTER JOIN @Department B
       ON     A.DeptID=B.DeptID
WHERE  A.DeptID IS NULL
OR     B.DeptID IS NULL

Cartesian Product Or Cross Join
Known as Join without condition
ResultSet = No of Rows in TableA * No of Rows in TableB
Display all the Possibilities of combinations.

SELECT *
FROM   @Employee
INNER JOIN @Department
ON 1 = 1

--Explicit
SELECT *
FROM   @Employee
CROSS JOIN @Department

--Implicit
SELECT *
FROM   @Employee, @Department

Self-Join
A table joins with itself with one or two aliases to stave off confusion are called self-join.

SELECT A.EmpName ,
       B.EmpName ,
       A.DeptID  ,
       B.DeptID
FROM   @Employee A
       INNER JOIN @Employee B
       ON     A.DeptID = B.DeptID