SQL 查询优化：查找与 SMITH 同职位但姓名不为 SMITH 的员工

SELECT e.ename, d.dname FROM emp e INNER JOIN (SELECT deptno, ename FROM emp WHERE job = (SELECT job FROM emp WHERE ename = 'SMITH')) d ON e.deptno = d.deptno WHERE e.ename != 'SMITH'