Hi , I have the following requirement . I need to join two tables based on condition 1 and pick field A from table2 and based on condition 2 and pick field b from table2. Please find below my query. Insert INTO TABLEB SELECT SR.COLUMNA FROM TABLEA SR LEFT JOIN TABLE B ML ON SR.COLUMNA = ML.COLUMNA UNION ALL SELECT SR.COLUMNB FROM TABLE A SR LEFT JOIN TABLEB ML ON SR.COLUMNB = ML.COLUMNA WHERE ML.COLUMNA IS NULL AND SR.COLUMNB IN NOT NULL GROUP BY SR.COLUMNA I have a doubt regarding the use of union all .Is it better to use union all or is there any better way to do THIS. I would like to mention that i have around 40 million rows .so performance is my main concern.
The union all is basically telling the query optimizer that it doesn't need to sort and remove duplicates from both the selects. This will save you a potentially large sort operation and will result in a quicker execution when compared to a "normal" union. However, the onus is on you to ensure that the data is then "clean".