I have a table of
Orders and another table of
Orders table will contain a reference to the
Deliveries table if there is a corresponding row.
Not every row in
Orders will have a row in
Deliveries (digital media, warranties, etc), but if an order does have a delivery, it will have only one. I am trying to combine data from these two tables into a third table (
OrderInformation) to be used for reporting.
I can create the final table one of two ways:
Inserting all orders into the
OrderInformationtable first and then updating only the rows that have a matching
Deliveriesrecord in a second statement.
Inserting all values into the table at once using a
LEFT OUTER JOINbetween
I tested this out on my own data and the 'LEFT OUTER JOIN' query consistently outperformed the 'UPDATE' query by 200%, which surprised me.
Which option would perform better in general? And why does it perform better that way? Is performance contingent on join conditions, the type of data involved, etc?