mysql - 改进这个多 UNION 查询的方法？

Question

我需要找到有这两种情况的用户

1. 共享视频信用；
2. 共享制作信用；或
3. 共享组。

这是我目前提出的查询:

SELECT profile_id FROM productions_productionmember WHERE production_id in 
    (SELECT production_id FROM productions_productionmember WHERE profile_id=?)
UNION
SELECT profile_id FROM groups_groupmember WHERE group_id in 
    (SELECT group_id FROM groups_groupmember WHERE profile_id=?)
UNION
SELECT profile_id FROM videos_videocredit WHERE video_id in 
    (SELECT video_id FROM videos_videocredit WHERE profile_id=?)

相关表格:

groups_groupmember
- profile_id
- group_id 

videos_videocredit
- profile_id
- video_id

productions_productionmember
- profile_id
- production_id

如何改进这个查询？

Answer 1

SELECT DISTINCT p.profile_id 
FROM productions_productionmember p
left outer join groups_groupmember g on g.profile_id = p.profile_id
left outer join videos_videocredit v on v.profile_id = p.profile_id
WHERE v.video_id is not null or g.group_id is not null
or p.production_id in (SELECT production_id 
                     FROM productions_productionmember 
                     WHERE profile_id=?)