⇒ Note

• To show who is PK and FK open DB diagram then right click on relation then properties.
• If you restored DB of someone Diagrams will not be appeared so right click on DB ⇒ properties ⇒ Files ⇒ write in Owner (sa).

Joins types

1. Cross Join:

This query returns the Cartesian product of employees and departments.

-- Cartesian product (Cross Join)
SELECT e.EmployeeName, d.DepartmentName
FROM Employee e
CROSS JOIN Department d;

2. Inner Join (Equi-Join):

This query fetches employees who belong to a department, using the foreign key DepartmentID.

-- Inner Join (Employees with Departments)
SELECT e.EmployeeName, d.DepartmentName
FROM Employee e
INNER JOIN Department d
ON e.DepartmentID = d.DepartmentID;

3. Outer Joins:

a. Left Outer Join:

Returns all employees and their departments, even if the employee is not assigned to any department (departments can be NULL).

-- Left Outer Join (All Employees with or without Department)
SELECT e.EmployeeName, d.DepartmentName
FROM Employee e
LEFT OUTER JOIN Department d
ON e.DepartmentID = d.DepartmentID;

b. Right Outer Join:

Returns all departments and their employees, even if no employee is assigned to that department (employees can be NULL).

-- Right Outer Join (All Departments with or without Employees)
SELECT e.EmployeeName, d.DepartmentName
FROM Employee e
RIGHT OUTER JOIN Department d
ON e.DepartmentID = d.DepartmentID;

c. Full Outer Join:

Returns all employees and departments, even if no matching pairs exist.

-- Full Outer Join (All Employees and Departments, even if no match)
SELECT e.EmployeeName, d.DepartmentName
FROM Employee e
FULL OUTER JOIN Department d
ON e.DepartmentID = d.DepartmentID;