/*** CREATE table Departments **/
CREATE TABLE Departments(
  DepartmentID INT IDENTITY(1,1) PRIMARY KEY,
  DepartmentName varchar(500)
);
/* Insert record into table Departments */
INSERT INTO Departments (DepartmentName) VALUES
('IT'),('HR'),('Payroll'),('Admin');

/** CREATE TABLE Employees **/

CREATE TABLE Employees(
  EmployeeID INT IDENTITY(1,1) PRIMARY KEY,
  EmployeeName varchar(500),
  DepartmentID INT DEFAULT NULL
);

/** Insert record into table Employees **/
INSERT INTO Employees (EmployeeName,DepartmentID) VALUES 
('Mark',1),('John',1),('Mike',1),('Mary',2),('Stacy',3),('Pam',null);

/** LEFT JOIN **/
SELECT em.EmployeeName,de.DepartmentName FROM Employees AS em
LEFT JOIN Departments AS de ON de.DepartmentID = em.DepartmentID;

/** RIGHT JOIN **/
SELECT em.EmployeeName,de.DepartmentName FROM Employees AS em
RIGHT JOIN Departments AS de ON de.DepartmentID = em.DepartmentID;

/** INNER JOIN **/
SELECT em.EmployeeName,de.DepartmentName FROM Employees AS em
INNER JOIN Departments AS de ON de.DepartmentID = em.DepartmentID;

/** FULL OUTER JOIN **/
SELECT em.EmployeeName,de.DepartmentName FROM Employees AS em
FULL OUTER JOIN Departments AS de ON de.DepartmentID = em.DepartmentID;