Transform `SELECT` .. `UNION ALL` `SELECT` .. into something more efficient?

Question

I have following query, which works, but I guess it can it be improved to something more efficient, how to do it?

all the values are from 1 table
1st part of the result are always 2 rows having some a relation forming 1 row with all the fields set
2nd part of the result are rows with some condition not participating in the first part which already have the right fields

    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'

Just some notes, row1 and row2 are very confusing _table_ aliases. Also, tables have _columns_, not fields. — jarlh, Oct 15 '20 at 11:32
In some way, the SQL data type date has a year field, a month field and a day field. — jarlh, Oct 17 '20 at 16:47

Joe Shark · Accepted Answer · 2020-10-15T15:20:26.180

2

*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'

edited Oct 15 '20 at 15:20

answered Oct 15 '20 at 11:37

Joe Shark

688
4
9

Thanks! I always had and have a problem thinking in SQL. It's kind of unstructured for me, I hope I get better in that over time. – Lukas Salich Oct 16 '20 at 23:43

Gordon Linoff · Answer 2 · 2020-10-15T12:55:03.050

0

I think you want a left join:

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;

This returns all rows from table1 along with matching columns from table2 if they exist.

edited Oct 15 '20 at 12:55

answered Oct 15 '20 at 11:34

Gordon Linoff

1,242,037
58
646
786

Seems like OP wants `coalesce(row2.field3, row1.field3) as field3`. – jarlh Oct 15 '20 at 11:37
this answer is incomplete as it does not use the extra condition. – Conffusion Oct 15 '20 at 16:05
@Conffusion . . . The implication is that the extra condition is used to identify the rows that do not match the first `join`. – Gordon Linoff Oct 15 '20 at 18:31

Transform `SELECT` .. `UNION ALL` `SELECT` .. into something more efficient?

2 Answers2