LEFT JOIN (or LEFT OUTER JOIN)

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

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

FROM Employee E

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

Explanation: Since all Designation_NO values in Employee match a Dept_ID in Department, the result is the same as INNER JOIN in this case. If there were employees with Designation_NO values not present in the Department table, those rows would have NULL in the Dept_Name column.