When I use UNION with a SELECT that LEFT JOIN's empty fields, I get invalid data in the rows of the other SELECT depending on UNION operand order.

In the case described below, family_fam has no row matching fam_ID=0, so it should return NULL for the value of all LEFT JOIN'ed fields. This is desired so that the UNION will work (matching columns for the SELECTs) but without returning any data in these columns.

If I put the SELECT with the empty LEFT JOIN'ed field values first, it works fine:

(SELECT * FROM person_per LEFT JOIN family_fam ON fam_ID = 0 WHERE per_fam_ID = 0) UNION (SELECT * FROM person_per LEFT JOIN family_fam ON fam_ID = per_fam_ID WHERE per_fam_ID > 0)

(note: I fully realize this example doesn't make real-world sense. I have stripped these two selects down to their absolute basics for ease of discussion.)

Both of these selects work properly by themselves as well. However, if I reverse the order of the SELECT's (ie. B UNION A), the fields which should be NULL due to the non-existant fam_ID = 0 row will now contain data from the LEFT JOIN of the *last row* of the first (B) SELECT statement.

Example data might look like this, where per_Name comes from person_per and fam_Name comes from family_fam

Working case:

per_Name, fam_Name

-------------------- Johnson, NULL Edwards, NULL Adams, Adams Blah, BlahFamily Stone, Richards

Non-working case:

per_Name, fam_Name

-------------------- Adams, Adams Blah, BlahFamily Stone, Richards Johnson, Richards <- These fam_Name values should be NULL like above! Edwards, Richards <-

This doesn't seem to be the prescribed behavior. Perhaps some variables internally are not being cleared within a loop somewhere?