⇒ Note
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;
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;
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;
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;
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;