asked
select query with or with out union all
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.
On the top half you aren't retreiving any columns from tableB and since it is a left joing tableB won't exclude any rows from TableA. Unless a row in tablea might match to multiple rows in tableb and you want to capture those repetitions then it seems the join could be removed from that top half without affecting the results...
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".
9 People are following this question.
