Extract all records even with missing "optional" fields in another table
What you need is an outer join, likely a left outer join. From the documentation 


  Specifies that all rows from the left table not meeting the join condition are included in the result set, and output columns from the other table are set to NULL in addition to all rows returned by the inner join.


For you the "left" table will be your product table and the other table is the attributes. The query will be

select &lt;some columns&gt;
from ICITEM as p
left outer join ICITEMO as o
  on o.FMTITEMNO = p.FMTITEMNO
  and o.OPTFIELD = 'COLOR';