Extract all records even with missing "optional" fields in another table I have a main product table ICITEM listing product IDs and another table ICITEMO (please don’t ask about the table names) with a number of optional product fields.  Not all optional fields exist for all products, for example:

ICITEM.FMTITEMNO
----------------
Product A
Product B
Product C
Product D



ICITEMO.ITEMNO | ICITEMO.OPTFIELD | ICITEMO.VALUE
---------------+------------------+---------------
 Product A     | COLOR            | Blue
 Pruduct A     | SIZE             | 12
 Product B     | COLOR            | Red
 Pruduct D     | SIZE             | 14


I need to retrieve all product IDs from the main ICITEM tables regardless if they have corresponding optional field(s) in the secondary table ICITEMO, so for example looking at the COLOR field:

ICITEM.FMTITEMNO | ICITEMO.VALUE
---------------- +--------------
Product A        | Blue
Product B        | Red
Product C        | NULL
Product D        | NULL


At the moment I have a rather ugly UNION query which works but I’m sure it can be done in a more professional and elegant way:

SELECT ICITEM.FMTITEMNO, ICITEMO.VALUE
FROM ICITEM, ICITEMO   WHERE ICITEM.ITEMNO = ICITEMO.ITEMNO  AND 
ICITEMO.OPTFIELD="COLOR" 

UNION

SELECT ICITEM.FMTITEMNO, NULL as GENDER
FROM ICITEM
WHERE ICITEM.FMTITEMNO NOT IN
(SELECT IC.FMTITEMNO
FROM ICITEM IC LEFT JOIN ICITEMO ON IC.ITEMNO = ICITEMO.ITEMNO
WHERE ICITEMO.OPTFIELD="COLOR")
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';