Search code examples
sqlpostgresqlunion-all

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


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

  1. all the values are from 1 table
  2. 1st part of the result are always 2 rows having some a relation forming 1 row with all the fields set
  3. 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'

Solution

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