I found a web page that says following:
Oracle SQL Tuning - DISTINCT vs. EXISTS:
Avoid joins that require the DISTINCT qualifier on the SELECT list in queries which are used to determine information at the owner end of a one-to-many relationship. The DISTINCT operator causes Oracle to fetch all rows satisfying the table join and then sort and filter out duplicate values. EXISTS is a faster alternative, because the Oracle optimizer realizes when the subquery has been satisfied once, there is no need to proceed further and the next matching row can be fetched.
Below query returns all department numbers and names which have at least one employee.
SELECT DISTINCT dept_no, dept_name
FROM dept D,
emp E
WHERE D.dept_no = E.dept_no; SELECT dept_no, dept_name
FROM dept D
WHERE EXISTS (
SELECT 'X'
FROM emp E
WHERE E.dept_no = D.dept_no);
( http://askanantha.blogspot.com/2007/10/sql-tuning-guidelines-for-oracle-simple.html )
What do you think of such rule, said out, that "distinct" with joins is bad, and "exists" is better?
I understand that it depends of data amounts between tables, and the health of indexes on tables. So that "distinct join" may become cheaper for some databases. Or can we generalize ar the statement was done still?