Inner JOIN

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

FROM Employee E

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

Explanation: The INNER JOIN returns only those rows where there is a match between Designation_NO in Employee and Dept_ID in Department.