sql / expert
Snippet
Multi-Set Intersections for Complex Requirements
Using INTERSECT to find rows that satisfy multiple independent conditions across different rows in the same table. This is often more performant and cleaner than self-joining the table multiple times for 'relational division' problems.
snippet.sql
1
2
3
4
5
SELECT user_id FROM user_permissions WHERE permission_id = 'READ_DATA'INTERSECTSELECT user_id FROM user_permissions WHERE permission_id = 'WRITE_DATA'INTERSECTSELECT user_id FROM user_permissions WHERE permission_id = 'ADMIN_ACCESS';
Breakdown
1
SELECT user_id FROM user_permissions WHERE ...
Each individual SELECT identifies users with one specific permission.
2
INTERSECT
The set operator that returns only the distinct values present in all result sets.