When to use DISTINCT
SELECT DISTINCT is sometimes an indication that someone has 'fixed' a query that returns duplicate rows in a naive way. This (mis-)use is probably more common among those with relatively little database experience.

That said, some people (Chris Date, for example) argue that DISTINCT should be the default qualifier for SELECT; we should have to write SELECT ALL to return a non-relational result (relations do not contain duplicate tuples by definition).

My view is that there is nothing inherently wrong with SELECT DISTINCT. It can be the most natural way to express a query. If the execution plan and performance is acceptable, I see no sensible reason to mandate that it should never be used.

At the same time, it is clearly wrong to simply add DISTINCT at the top level of a complex query to eliminate duplicates at a late stage, when the query should be simply written more efficiently.