What is better, do a union and then a join o do a join in all the tables of the union?

Question

I have to do a union of 5 tables and then do a join with other table. Which way have more efficiency do the union of the 5 tables and then the join o do the join on each tables and then de union?.

Answer 1

I'm tempted to close this question as two broad. But let me point out why you have not provided enough information.

First, you specify "union" rather than "union all", which means that your query needs to remove duplicates. That is an expensive operation. Do you want to do it before or after the join?

• If there are many matches in the other table for a given value in union query, then you want to remove duplicates before the join.
• If the join to the other table filters out many rows from the union query, then you want to join first and remove after.

Indexing matters as well.

• If you have only an index on the other table, then doing the union first might be a good idea.
• If you have only indexes on the tables in the union, then you might want to split apart the union into five separate join queries.

Just based on these two issues, it is not possible to provide a simple answer to your question. And, there are many other considerations as well -- table partitioning, filtering, aggregation, order by, etc.

Answer 2

That is a very nice question. I test the question on two server configuration:

For Single Thread Server : join after union was much better than join 5 tables and then union the result because for every join you should do the same index scan.

For Multi Thread Server : join Before union was much better because of the max degree of parallelism. All joins will be processed at the same time then the union will happen.

Join After Union

Join Before Union