这里有两条sql语句 小弟我想取他们的并集

这里有两条sql语句 我想取他们的并集
1.
select a.bomcomponent,a.quantity,a.classtype,a.speprocuretypefbomitem from COC.MBOMITEM a where (materialbill, bomitemnodeno) in (select materialbill,bomitemnodeno
from COC.MBOMSITEMSELECT where materialbill='9166' 
and (a.speprocuretypefbomitem <>'' or a.speprocuretypefbomitem is not null))
2.
select a.bomcomponent,a.quantity,a.classtype,a.speprocuretypefbomitem,bb.specprocuretype from COC.MBOMITEM a 
join COC.PLANTMTL bb on a.bomcomponent=bb.mtlno
where (bb.specprocuretype <>'' or bb.specprocuretype is not null)
and (a.materialbill, a.bomitemnodeno) in (select materialbill,bomitemnodeno
from COC.MBOMSITEMSELECT where materialbill='9166') 

最好可以给优化一下 我看着都晕了

[解决办法]

SQL code

1SELECT a.bomcomponent, a.quantity, a.classtype, a.speprocuretypefbomitem  FROM coc.mbomitem a WHERE (materialbill, bomitemnodeno) IN (          SELECT materialbill, bomitemnodeno            FROM coc.mbomsitemselect           WHERE materialbill = '9166'             AND (   a.speprocuretypefbomitem <> ''                  OR a.speprocuretypefbomitem IS NOT NULL                 ))2SELECT a.bomcomponent, a.quantity, a.classtype, a.speprocuretypefbomitem,       bb.specprocuretype  FROM coc.mbomitem a JOIN coc.plantmtl bb ON a.bomcomponent = bb.mtlno WHERE (bb.specprocuretype <> '' OR bb.specprocuretype IS NOT NULL)   AND (a.materialbill, a.bomitemnodeno) IN (                                            SELECT materialbill,                                                   bomitemnodeno                                              FROM coc.mbomsitemselect                                             WHERE materialbill = '9166')
[解决办法]

1用exist试试，不是绝对能快，但大部分时候能快
SQL code
SELECT a.bomcomponent, a.quantity, a.classtype, a.speprocuretypefbomitem  FROM coc.mbomitem a WHERE exists (          SELECT 1            FROM coc.mbomsitemselect b           WHERE materialbill = '9166'             AND (   a.speprocuretypefbomitem <> ''                  OR a.speprocuretypefbomitem IS NOT NULL                 )         a.materialbill=b.materialbill and  a.bomitemnodeno=a. bomitemnodeno)
[解决办法]
2
SQL code
SELECT a.bomcomponent, a.quantity, a.classtype, a.speprocuretypefbomitem,       bb.specprocuretype  FROM coc.mbomitem a, coc.plantmtl bb WHERE a.bomcomponent = bb.mtlno   AND (bb.specprocuretype <> '' OR bb.specprocuretype IS NOT NULL)   AND EXISTS (          SELECT 1            FROM coc.mbomsitemselect b           WHERE materialbill = '9166'             AND a.materialbill = b.materialbill             AND a.bomitemnodeno = b.bomitemnodeno)
[解决办法]
1 union 2 ,就可以了呀
[解决办法]
想取并集的话还可以 用 Union All