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')

------解决方案--------------------

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