How to determine which is more efficient: DISTINCT or WHERE EXISTS?

Question

For example, I have 3 tables: user , group and permission and two polysemantic relationships between them: user_groups and group_permissions .

I need to select all permissions of this user without repetition. Every time I encounter a similar problem, I cannot determine which version of the request is better:

 SELECT permisson_id FROM group_permission WHERE EXISTS( SELECT 1 FROM user_groups WHERE user_groups.user_id = 42 AND user_groups.group_id = group_permission.group_id ) SELECT DISTINCT permisson_id FROM group_permission INNER JOIN user_groups ON user_groups.user_id = 42 AND user_groups.group_id = group_permission.group_id 

I have enough experience to draw conclusions based on the explanation. The first query has a subquery, but my experience has shown that the first query is faster. Perhaps due to the large number of filtered permissions as a result.

What would you do in this situation? What for? Thanks!

Answer 1

Use EXISTS instead of DISTINCT

You can suppress the display of duplicate lines with DISTINCT; you use EXISTS to check for the existence of rows returned by a subquery. When possible, you should use EXISTS rather than DISTINCT, because DISTINCT sorts the extracted rows before suppressing duplicate rows.

in your case there will be a lot of duplicated data so that the creature is faster.

http://my.safaribooksonline.com/book/-/9780072229813/high-performance-sql-tuning/ch16lev1sec10