Use EXISTS in Place of DISTINCT

Use EXISTS in Place of DISTINCT  

                   

•Avoid joins that require the DISTINCT qualifier on the SELECT list when you submit queries used to determine information at the owner end of a one-to-many relationship (e.g. departments that have many employees).

 Least Efficient :

   SELECT         DISTINCT DEPT_NO, DEPT_NAME

  FROM            DEPT D, EMP E

  WHERE          D.DEPT_NO = E.DEPT_NO

 Most Efficient :

   SELECT DEPT_NO, DEPT_NAME

  FROM    DEPT D

  WHERE EXISTS (SELECT       ‘X’

                      FROM         EMP E

                      WHERE       E.DEPT_NO = D.DEPT_NO);

•EXISTS is a faster alternative because the RDBMS kernel realizes that when the sub-query has been satisfied once, the query can be terminated.

Note : Provide your comments by clicking below options! Thanks ! :)

Tweet