Add null row only if there's no result for such pe_num

I have several queries union'ed together, they all share the same key - pe_num(integer). I use UNION everywhere, but sub-queries themselves have INNER JOINS in them(they are seeking instances of certain type in other tables linked with the object), so if there is no instance of some type for this object, corresponding query will return no results at all. Resulting table must have ALL the pe_num's, in some way e.g. if there are no instances of any type for this object,开发者_JAVA百科 null row must be added(ONLY ONE null row), and if there are instances of type 1, but there are no instances of type 2, query for type 2 must not return a null row.

If i have sixteen objects with different pe_num's, all the sixteen numbers must be in the table(they sometimes duplicate, but none must be missing).

What is the general approach for achieving this results?

I'm not sure that I'm reading your question correctly, but it seems to me that you have a database schema which models some inheritance relationships, so I'll assume that for the rest of my answer.

Your question (if I understand it) is about how to determine which derived classes some set of objects are instances of -- the objects in question are in the base table with a primary key of pe_num, and the derived tables all have a foreign key of pe_num.

Desired result -- columns: pe_num, derived_class; return at least one row per pe_num; if no derived instances for that pe_num, 'derived_class' is NULL

Strategy: follow your basic procedure involving UNION's (as you mentioned above) to find all derived classes that the base object is an instance of. As you noted, there may be some rows missing. So 'LEFT JOIN' the desired rows to the subclass results ('LEFT JOIN' is similar to an 'INNER JOIN' but if it finds no matching row on the right side, it simply inserts a row with the value from the left and a NULL on the right -- joins in MySQL):

SELECT pe_num, derived_class
FROM base_table
LEFT JOIN ( ** some complicated subquery involving UNION's as mentioned in OP **)
USING (pe_num)