EXISTS vs DISTINCT

It is alwasys prefered not to use DISTINCT qualifier on the SELECT statement where we need to determine the information from owner end means where one to many relationship exists.
For exmaple if we take departments from EMP table, then we see that departments has one to many relationship with employees column in EMP table.
Now if we want to show the result like all department numbers from EMP table and department name from DEPT table then deprtment number is only column where we can make a join to these two tables.

We get duplicate records when we execute only with deptno column as below:

SQL>SELECT E.DEPTNO, D.DNAME
FROM DEPT D, EMP E
WHERE D.DEPTNO = E.DEPTNO;

OUTPUT:

DEPTNO DNAME
10 ACCOUNTING
30 SALES
10 ACCOUNTING
20 RESEARCH
20 RESEARCH
20 RESEARCH
20 RESEARCH
30 SALES
30 SALES
30 SALES

But the above result is not that good what we want to see , by using DISTINCT we get the proper result where it removes the duplicate rows like as below:

SQL>SELECT DISTINCT E.DEPTNO, D.DNAME
FROM DEPT D, EMP E
WHERE D.DEPTNO = E.DEPTNO;

OUTPUT:
DEPTNO DNAME
10 ACCOUNTING
20 RESEARCH
30 SALES
But to the same query if we can modify by using EXISTS then it will be much more faster and efficient because RDBMS kernel understand that if the sub-query has been satisfied once then the query should be terminated.
So let us see how we can use EXISTS in place of DISTINCT:

SQL>SELECT deptno, dname
FROM dept d
WHERE EXISTS (SELECT x
FROM emp e
WHERE e.deptno = d.deptno);

OUTPUT:
DEPTNO DNAME
10 ACCOUNTING
20 RESEARCH
30 SALES

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s