对于取多个枚举字段，select 中带子查询 和 全部用left join 哪个好呢？

我们有一个枚举表SY_ECODE。
但是一条数据的七八个字段都是枚举选项，数据库存的是枚举编码，每次查询数据取对应的枚举值，如果left join 的话，就要七八次，就如下边的SQL。我感觉用子查询的话好看一点，写起来也方便，不然的话l要eft join好多次，到底怎么优化呢？

select
        (select e.ECODETEXT from SY_ECODE e where e.ecode = o.otsstatus and e.ecodetype = 'SQM_OTS_STATUS') otsstatus,
        (select e.ECODETEXT from SY_ECODE e where e.ecode = c.conclusion and e.ecodetype = 'SQM_OTS_CONCLUSION') conclusion,
        (select e.ECODETEXT from SY_ECODE e where e.ecode = p.parttype and e.ecodetype = 'SQM_APQP_PART_TYPE') parttype,
        (select e.ECODETEXT from SY_ECODE e where e.ecode = p.professional and e.ecodetype = 'SQM_APQP_PROFESSION') professionalText,
        (select e.ECODETEXT from SY_ECODE e where e.ecode = p.developtype and e.ecodetype = 'SQM_APQP_PART_DEVTYPE') developtype,
        (select e.ECODETEXT from SY_ECODE e where e.ecode = j.PROJECTLEVEL and e.ecodetype = 'SQM_APQP_PROJECT_LEVEL') PROJECTLEVEL,
        (select pf.platform from GPIS_BASE_PLATFORM pf where pf.id = p.platformid) platformText,
        (select u.usertext from sy_user u where u.usercode = a.DEVUSERCODE ) devUserText,
        (select u.usertext from sy_user u where u.usercode = a.SEUSERCODE) seUserText,
        (CASE WHEN p.isSingleVehicle = '0' THEN '否' WHEN p.isSingleVehicle = '1' THEN '是' ELSE '' END ) isSingleVehicle,
        o.id,o.otscode,a.projectid,j.PROJECTNAME,a.apqpCode,ap.subapqpcode,p.contractPartName,p.PARTNO,p.PARTNAME,
        ap.PLANFINISHDATE,p.vehiclenames,ap.FINISHDATE
        from
        SQM_APQP_OTS o
        left join SQM_OTS_CONCLUSION_ISSUED c on c.otscode = o.otscode
        left join SQM_APQP_INFO a on a.id = o.apqpid
        left join SQM_APQP_ASSOCIATE_PARTS ap on ap.apqpcode = a.APQPCODE
        left join SQM_APQP_PARTS p on p.id = ap.partid
        left join SQM_APQP_PROJECT j on j.id = a.projectid

带子查询 和 left join都可以

left join比子查询效率高，最好是使用left join

个人觉得此类问题基本从两个角度考虑，1）运行效率，2）代码可读性和可维护性
对于枚举值，我个人的做法是先用小查询把枚举值查出来，然后再在查询里使用，这样关联表少，查询速度也快，可读性也高。
当然还是要具体问题具体分析，如果你需要全部枚举值参与的，那自然我的做法就不能使用。但退一步来说，如果你总是要把这么多表join起来，是不是和通常我们反对的SELECT * 一样，是不是该重新考虑下设计呢？^-^