Why We should Use EXISTS instead of DISTINCT to improve Query Performance

Today, I am going to give you a tip to improve the query performance when you are using DISTINCT to eliminate duplicate records.

My team member wants to find all the department names where each department has at least one employee.

So his query is:
SELECT distinct D.DEPARTMENT_ID,D.DEPARTMENT_NAME
FROM DEPARTMENT D INNER JOIN EMPLOYEE E
ON D.DEPARTMENT_ID = E.DEPARTMENT_ID

This is Ok where my team member is joining Employee and Department table and then taking distinct department Names and IDs.

Lets look at the execution plan:


Estimated Sub Tree Cost : 0.0182019
Query Cost : 73%

My Query is:
When I look at the requirement little bit close, the requrement says If the department has atleast one employee then return the department Name. The perfect solution which satisfies this requirement is with EXISTS.

So I modified the query with the EXISTS clause.

SELECT D.DEPARTMENT_ID,D.DEPARTMENT_NAME
FROM DEPARTMENT D WHERE EXISTS (SELECT 1 FROM EMPLOYEE E WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID)

Lets look at the execution plan:


Estimated Sub Tree Cost : 0.0067844
Query Cost : 27%

As you can see from the results there is signifact of difference in the Query Cost.

So use EXISTS instead of DISTINCT. Also it doesn't mean that it EXISTS can be used at all the places where you have DISTINCT. This basically on case by case.