--Maximum no of records by INNER JOIN,LEFT JOIN,RIGHT JOIN & FULL JOIN

--SQL INTREVIEW QUESTION

--Asked by Synchron

----------------------------------------------------------------------------------

USE [SSIS]
Create table Synechron_TABL
(
     ID int ,
     DepartmentName nvarchar(50),
     Location nvarchar(50),
     DepartmentHead nvarchar(50)
)
Go
--DROP TABLE Synechron_TABL
Insert into Synechron_TABL values (1, 'IT', 'London', 'Rick')
Insert into Synechron_TABL values (1, 'Payroll', 'Delhi', 'Ron')
Insert into Synechron_TABL values (1, 'HR', 'New York', 'Christie')
Insert into Synechron_TABL values (1, 'Other Department', 'Sydney', 'Cindrella')
Go
--DROP TABLE Synechron_TABL_2
Create table Synechron_TABL_2
(
     ID int ,
     Name nvarchar(50),
     Gender nvarchar(50),
     Salary int
     
)
Go
Insert into Synechron_TABL_2 values (1, 'Tom', 'Male', 4000)
Insert into Synechron_TABL_2 values (1, 'Pam', 'Female', 3000)
Insert into Synechron_TABL_2 values (1, 'John', 'Male', 3500)
Insert into Synechron_TABL_2 values (1, 'Sam', 'Male', 4500)
Insert into Synechron_TABL_2 values (1, 'Todd', 'Male', 2800)
Insert into Synechron_TABL_2 values (1, 'Ben', 'Male', 7000)
Insert into Synechron_TABL_2 values (1, 'Sara', 'Female', 4800)
Insert into Synechron_TABL_2 values (1, 'Valarie', 'Female', 5500)
Insert into Synechron_TABL_2 values (1, 'James', 'Male', NULL)
Insert into Synechron_TABL_2 values (1, 'Russell', 'Male', NULL)
Insert into Synechron_TABL_2 values (1, 'PINTU', 'Male', NULL)
SELECT * FROM Synechron_TABL_2
SELECT * FROM Synechron_TABL

SELECT  COUNT (*)  as 'INNER JOIN' FROM Synechron_TABL_2 T2
INNER JOIN Synechron_TABL T1
ON T1.ID=T2.ID

SELECT  COUNT (* ) AS 'LEFT_JOIN_COUNT' FROM Synechron_TABL_2 T2
LEFT JOIN Synechron_TABL T1
ON T1.ID=T2.ID

SELECT  COUNT (*) AS 'RIGHT_OUTE_JOIN'   FROM Synechron_TABL_2 T2
RIGHT JOIN Synechron_TABL T1
ON T1.ID=T2.ID

SELECT  COUNT (*) AS 'FULL_OUTER_JOIN'   FROM Synechron_TABL_2 T2
FULL  JOIN Synechron_TABL T1
ON T1.ID=T2.ID