If you wish to retrieve from the department table all the department numbers that have at least one employee assigned to them, write the query as:
select deptno, deptname from dept where deptno in ( select deptno from emp) ;This query will run a full table scan on both the emp and dept tables. Even if there was an index on the deptno column of emp table, the sub-query would not be using the index and, hence, performance would suffer.
We can rewrite the query as:
select deptno, deptname from dept where exists in ( select deptno from emp where dept.deptno = emp.deptno) ;This query uses an existing deptno index on the emp table, making the query much faster. Thus, wherever possible use EXISTS in place of IN clause in a query.
Charlie has over a decade of experience in website administration and technology management. As the site admin, he oversees all technical aspects of running a high-traffic online platform, ensuring optimal performance, security, and user experience.
