SQL JOINS

Inner join

SELECT Emp.Empid, Emp.EmpFirstName, Emp.EmpLastName,

Dept.DepartmentName FROM Employee Emp INNER JOIN 

Department dept ON Emp.Departmentid=Dept.Departmentid 

Self Join

SELECT Emp1.Empid, Emp1.EmpFirstName+' '+Emp1.EmpLastName as 

EmployeeName, Emp2.EmpFirstName+' '+Emp2.EmpLastName as 

ManagerName FROM Employee Emp1 INNER JOIN Employee Emp2 ON 

Emp1.Managerid=Emp2.Empid 

Outer Join

1. Left Outer Join
2. Right Outer Join
3. Full Outer Join 

   Left Outer Join

   SELECT Emp.Empid, Emp.EmpFirstName, Emp.EmpLastName, Dept.DepartmentName FROM Employee Emp LEFT OUTER JOIN Department dept ON Emp.Departmentid=Dept.Departmenttid
   

   Right Outer Join

   SELECT Dept.DepartmentName, Emp.Empid, 

   Emp.EmpFirstName, Emp.EmpLastName FROM 

   Employee Emp RIGHT OUTER JOIN Department dept ON 

   Emp.Departmentid=Dept.Departmentid 

   Full Outer Join

   SELECT Emp.Empid, Emp.EmpFirstName, 

   Emp.EmpLastName, Dept.DepartmentName FROM

    Employee Emp FULL OUTER JOIN Department dept ON 

   Emp.Departmentid=Dept.Departmenttid 

   Cross Join

   SELECT Emp.Empid, Emp.EmpFirstName, Emp.EmpLastName, 

   Dept.DepartmentName FROM Employee Emp CROSS JOIN 

   Department dept