Many-to-many SQL join patterns with bridge tables, aggregation, filtering, and index guidance.
Bridge tables connect entities like users and roles or products and tags.
Resolve many-to-many relationships with two joins.
SELECT u.user_id, u.email, r.role_name
FROM users u
JOIN user_roles ur ON ur.user_id = u.user_id
JOIN roles r ON r.role_id = ur.role_id;The bridge table stores relationship rows and often uses a composite key.
Count related entities after joining through the bridge.
SELECT p.post_id, p.title, COUNT(pt.tag_id) AS tag_count
FROM posts p
LEFT JOIN post_tags pt ON pt.post_id = p.post_id
GROUP BY p.post_id, p.title;Use `LEFT JOIN` if you want posts with zero tags to remain visible.
Join and group, then compare distinct matched tags to the required count.
SELECT pt.post_id
FROM post_tags pt
JOIN tags t ON t.tag_id = pt.tag_id
WHERE t.name IN ('sql', 'performance')
GROUP BY pt.post_id
HAVING COUNT(DISTINCT t.name) = 2;This pattern is common in faceted search and content filtering.
Schema tips for bridge tables.
Index both sides of the relationship for join speed.
CREATE INDEX idx_user_roles_user_id ON user_roles(user_id);
CREATE INDEX idx_user_roles_role_id ON user_roles(role_id);Bridge tables are heavily joined and benefit from focused indexes.