I have following query, which works, but I guess it can it be improved to something more efficient, how to do it?
SELECT
row1.field1 AS field1,
row1.field2 AS field2,
row2.field3 AS field3,
FROM
table1 row1
JOIN
table1 row2
ON
row1.field = row2.field_reference
UNION ALL
SELECT
field1,
field2,
field3,
FROM
table1
WHERE
condition = 'I_am_sure_those_rows_are_not_returned_in_the_SELECT_above'
*Edited after reading @jarlh comment on another answer - should be COALESCE.
How about this - using a LEFT JOIN plus an OR statement in WHERE clause should return all those in row2 table AND all those meeting whatever your other condition is.
SELECT
row1.field1 AS field1,
row1.field2 AS field2,
coalesce(row2.field3, row1.field3) as field3
FROM table1 row1
LEFT JOIN table1 row2 ON row1.field = row2.field_reference
WHERE
row2.field_reference is not null
OR condition = 'I_am_sure_those_rows_are_not_returned_in_the_SELECT_above'