A blog about things that I find interesting, from my adventures around the world from 4 wheel driving around Australia's remote desert areas to motorcycling around in the Arctic or really anything that I feel like sharing!

Ok, perhaps it might seem strange to do this. Who would write such a query? Why bother with the join at all?

Well, if you imagine that someone had created a view which made available columns from both tables, but the consumer of the view didn't select any columns from the product table, then would the query executor really need to still make that join?

The answer is:

It depends on:

1 We know that with LEFT JOINs, that if the product table happened not to have a matching record for a sale record that the semantic of LEFT JOIN would cause NULL values to appear in the result set for columns from the table being LEFT JOIN.

2. Another affect that the LEFT JOIN could have would be that, if there happened to be more than 1 record matching a given product ID, then the join would match both of those rows to the sale table's row and cause the row in the sale table to be duplicated.

Item 1 would really not matter to us, since the join would only appear to be surplus to requirements none of the product table's columns were used in either the SELECT clause, WHERE clause etc.

Item 2 really could be a problem as removing the join in if join would cause duplicate sale rows to be produced in the results would cause query results to change, which is obviously not acceptable!

If we were able to prove that these duplicate rows were not possible, then we'd be ok to remove that join. Luckily this is quite easy to prove in this case as in the example above the product_id is the primary key of the product table, therefore there can never be any duplicates product_id values in the table. So we can be certain that he two queries below will produce the same results: