Our correlated subquery validates whether the values in the EMP_DEPT column exist or don’t exist, and thereby it serves the filter to the result set for the outer query. Other than selecting the columns, it also lets you perform DML operations such as updating or deleting multiple rows in other tables. You should have unique indexes on these join columns because correlated subqueries run for each row returned by the outer query. Unique indexes let correlated queries perform faster with fewer computing resources.
If you are using the correlated sub query, it is recommended to use EXISTS, IN, and =, in that order to get better performance.
Let's insert following row and execute the query again:
/*Additional row insert*/INSERT ...
