mysql - Mysql将表A的多个列与另一个表B连接

通过将同一张表连接两次，我有一个相同的解决方案，但我想知道是否可以优化以下查询？

select oc.name as father_occupation, o.name as mother_occupation
from user_family uf
left join occupations oc on uf.father_occupation = oc.occupation_id
left join occupations o on uf.mother_occupation = o.occupation_id
where user_id = 1;

最佳答案

您只需在一个左连接中检查两个条件

   select oc.name as father_occupation, o.name as mother_occupation
    from user_family uf
    left join occupations oc on uf.father_occupation = oc.occupation_id and uf.mother_occupation = oc.occupation_id where user_id = 1;