RIGHT JOIN (or RIGHT OUTER JOIN)

Description: Returns all records from the right table (Department), and the matched records from the left table (Employee). The result is NULL on the left side when there is no match. 

SELECT E.Emp_Name, E.Designation, D.Dept_Name

FROM Employee E

RIGHT JOIN Department D ON E.Designation_NO = D.Dept_ID;

Explanation: The RIGHT JOIN returns all departments, including "Legal," which has no employees associated with it. The Emp_Name and Designation fields are NULL for the "Legal" department.